Sample Header Ad - 728x90

How can I obtain continuous results with a PostgreSQL query?

0 votes
1 answer
567 views
I am using PostgreSQL version 8.1. I have a table of dates and tasks. The tasks have start times and end times in the table. The query below does not work as expected. SELECT sites.abbrev, (SELECT count(*) FROM ( SELECT DISTINCT measurements.task FROM measurements JOIN tasks ON tasks.id = measurements.task WHERE measurements.ztime >= '2016-10-10' AND measurements.ztime = '2016-10-10' AND measurements.ztime <= '2016-10-15') WHERE sites.abbrev = 'AA-10' GROUP BY sites.id, sites.abbrev, measurements.ztime::date ORDER BY measurements.ztime::date; The results are shown below: abbrev | num_tasks | total_time --------+-----------+------------ AA-10 | 62 | 36 AA-10 | 62 | 5 AA-10 | 62 | 58 AA-10 | 62 | 28 AA-10 | 62 | 17 (5 rows) Each row corresponds to one 24 hour period (Ex. 2016-10-10 to 2016-10-15). The problem is that I need num_tasks to show me the results for each 24 hour period, not the total number as shown. PostgreSQL version 8.1 does not allow for dates to be used with generate_series and there are no windowing functions available either. How can I modify my query so I can achieve the desired results?
Asked by GIS Student (11 rep)
Oct 18, 2016, 09:00 PM
Last activity: Oct 18, 2016, 09:33 PM