Postgres SQL - Add empty tuples when grouping query by hour

Question

I am writing a query to group events by hour on a table. My problem is that during hours in which there was no activity, no line is shown for them

For example I am getting:

|2015-04-07 08:00:00 | 2
|2015-04-07 09:00:00 | 1
|2015-04-07 11:00:00 | 1

when I would like to add an empty row with 0 values for hours in which no data exists:

|2015-04-07 08:00:00 | 2
|2015-04-07 09:00:00 | 1
|2015-04-07 10:00:00 | 0  <-- ADD EMPTY ROW
|2015-04-07 11:00:00 | 1

My query is:

   SELECT date_trunc('hour', "timeStarted"::timestamp) as "DATE STARTED", COUNT(*) 
FROM session s 
WHERE "timeStarted" BETWEEN timestamp with time zone '2015-04-07 00:00:00+01' AND timestamp with time zone '2015-04-07 23:59:00+01'
GROUP BY date_trunc('hour', "timeStarted"::timestamp) 
ORDER BY "DATE STARTED"

Answer 1

You can achieve this with function generate_series. PostgreSQL documentation - 9.24. Set Returning Functions:

SELECT generate_series, COUNT("timeStarted") 
FROM generate_series('2015-04-07 00:00:00+01', '2015-04-07 23:59:00+01', '1 hour'::interval)
LEFT JOIN session s ON date_trunc('hour', "timeStarted"::timestamp) = generate_series
    AND "timeStarted" BETWEEN timestamp with time zone '2015-04-07 00:00:00+01' AND timestamp with time zone '2015-04-07 23:59:00+01'
    GROUP BY generate_series
ORDER BY generate_series;

Note that the explicitly stated time range on timeStarted in the JOIN condition is redundant. If index is used on timeStarted the explicit time range makes sure that it is being used in the query (if deemed so by the query planner).