Postgres query - 24 hour time series filtered by specific column, but still return row for each hour

Question

I'm working on a query that returns an hourly time series for a given day, but I need to filter by a particular column on another table, which in my case is the user id.

This is my current query, which returns the submission count for every hour of the current day:

SELECT hours, count(s.id)
FROM generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
         left join submission s on hours = date_trunc('hour', s.submitted_date)
group by hours
order by hours;

However, these records are irrespective of user id, so when I filter by user id with a query like this it will only return the hours for which a submission for that user was received when instead I need it to return records for every hour much like the query above:

SELECT hours, count(s.id)
FROM generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
    left join submission s on hours = date_trunc('hour', s.submitted_date)
    left join form f on s.form_custom_id = f.custom_id
    left join "user" u on f.user_id = u.id
    where u.id = 4
group by hours
order by hours;

Here's my SQL Fiddle: http://www.sqlfiddle.com/#!17/a8d80/1

Any help would be greatly appreciated!

Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Put what is needed to ask in your post, not just at link/fiddle. A [mre] includes cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Explain what the parts you can do do & how they relate to your goal. — philipxy, Jul 12 '20 at 21:58

score 3 · Accepted Answer · answered Jul 12 '20 at 20:53

You want to move the filtering condition on the user to the on side of the join. Accordingly, you need to count on a column from the user table, so the filtering logic is taken into account:

select hours, count(u.id)
from generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours
left join submission s on hours = date_trunc('hour', s.submitted_date)
left join form f on s.form_custom_id = f.custom_id
left join "user" u on f.user_id = u.id and u.id = 4
group by hours
order by hours;

This did the trick, what a subtle but important difference. Much appreciated! — Tim Wheeler, Jul 12 '20 at 20:59

score 1 · Answer 2 · answered Jul 12 '20 at 20:25

For a left join, the filtering on all but the first table needs to be in the on clause:

select hours, count(s.id)
from generate_series(current_date, current_date + interval '23h', cast('1h' as interval)) hours left j oin
     submission s 
     on hours = date_trunc('hour', s.submitted_date) left join
     form f
     on s.form_custom_id = f.custom_id left join
     "user" u
     on f.user_id = u.id and u.id = 4
group by hours
order by hours;

Otherwise, the where clause turns filters out the non-matching rows, turning the outer join into an inner join.

Thanks for the quick reply. After using your example it seems it still returns records irrespective of the user id. — Tim Wheeler, Jul 12 '20 at 20:45

Postgres query - 24 hour time series filtered by specific column, but still return row for each hour

2 Answers2