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?

• user1822: Postgres 8.1 is long dead and forgotten - it's time to update to a supported and maintained version.
• GIS Student: I am unable to update to a supported and maintained version or else I would have. Thanks for the editing assist.
• ypercubeᵀᴹ: Is ztime a DATE? It would be best if you added the CREATE TABLE statements in the question,
• a_vlad: If You create data structure in SQLFiddle it will be more easy to help, but general mistake it is subquery for numtask it not depend from main query - it always calculate same data. You must use any reference to parent query - for example name parent measurement t1, subquery measurement t2 - it will be WHERE t2.date = t1.date
• ypercubeᵀᴹ: Does the num_tasks have to count all tasks, even those that do not belong to the site with 'AA-10'?

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 ;

• a_vlad: what if sites.abbrev != 'AA-10'?
• ypercubeᵀᴹ: Does the num_tasks have to count all tasks, even those that do not belong to the site with 'AA-10'?
• GIS Student: No ypercube, the num_tasks only has to count tasks for the corresponding site. In this case, that would be AA-10. I am going to try your answer above.
• GIS Student: The solution provided by ypercube worked! Thank you very much! Issued solved. The query is very fast too!