Postgres SQL - Add empty tuples when grouping query by hour

Vote count:

0

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"

asked 1 min ago

Adrian E

1

Postgres SQL - Add empty tuples when grouping query by hour