title: Postgres query - 24 hour time series filtered by specific column, but still return row for each hour
body: <p>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.</p>
  <p>This is my current query, which returns the submission count for every hour of the current day:</p>
  <pre><code>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;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/L90DQ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/L90DQ.png" alt="enter image description here" /></a></p>
  <p>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:</p>
  <pre><code>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 &quot;user&quot; u on f.user_id = u.id
      where u.id = 4
  group by hours
  order by hours;
  </code></pre>
  <p><a href="https://i.stack.imgur.com/WmHI9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WmHI9.png" alt="enter image description here" /></a></p>
  <p>Here's my SQL Fiddle: <a href="http://www.sqlfiddle.com/#!17/a8d80/1" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!17/a8d80/1</a></p>
  <p>Any help would be greatly appreciated!</p>