How can I obtain continuous results with a PostgreSQL query?

Question

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-15'
            AND tasks.site = sites.id
        ) ALIAS
      ) AS num_tasks,
      count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   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?

Answer 1

I don't think you need the subquery. Would this work?

SELECT sites.abbrev,
       measurements.ztime::date AS period_start,
       count(DISTINCT measurements.task) AS num_tasks,
       count(*) AS total_time
FROM sites
  JOIN tasks ON tasks.site = sites.id
  JOIN measurements
    ON (measurements.task = tasks.id
   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 ;