Postgres: Group by hour and append the missing hours to the result with 0 counts-CodePudding

I have the following query, which returns the values correctly.

SELECT date_trunc('hour', created_at)                                     as date,
       CAST(COUNT(case when subscription = true then id end) AS INTEGER)  as subscriptions,
       CAST(COUNT(case when subscription = false then id end) AS INTEGER) as unsubscriptions
FROM requests
GROUP BY date
ORDER BY date

Output:

date	subscriptions	unsubscriptions
2021-01-14 23:00:00.000000 00:00	1	2
2021-01-14 20:00:00.000000 00:00	10	1
2021-01-14 18:00:00.000000 00:00	0	1
...	...	...

The problem is that i need the missing hours of the day even if there are not any entries in the table, with 0 count.

Expected output:

date	subscriptions	unsubscriptions
2021-01-14 23:00:00.000000 00:00	1	2
2021-01-14 22:00:00.000000 00:00	0	0
2021-01-14 21:00:00.000000 00:00	0	0
2021-01-14 20:00:00.000000 00:00	10	1
2021-01-14 19:00:00.000000 00:00	0	0
2021-01-14 18:00:00.000000 00:00	0	1
...	...	...

Table Structure:

id	subscription	created_at
int	bool	timestamp

CodePudding user response：

You will have to create a timeseries for the timeseries you want to report and LEFT JOIN your table with it. You can use generate_series to create that.

select generate_series AS generated_hour
  from generate_series
        ( '2021-01-14'::timestamp 
        , '2021-01-15'::timestamp
        , '1 hour'::interval)

Full example:

WITH YOUR_TABLE AS (
  SELECT NOW() AS created_at, TRUE AS subscription
  UNION SELECT NOW() - interval '3 hour', FALSE
  UNION SELECT NOW() - interval '5 hour', TRUE
  UNION SELECT NOW() - interval '5 hour', FALSE
  UNION SELECT NOW() - interval '8 hour', FALSE
),

TIME_SERIES AS (
  SELECT GENERATE_SERIES AS hour
  FROM GENERATE_SERIES
        ( CURRENT_DATE
        , CURRENT_DATE   interval '1 day'
        , '1 hour'::interval))
  
SELECT TIME_SERIES.hour, 
   SUM(CASE WHEN subscription = true THEN 1 ELSE 0 END)  as subscriptions,
   SUM(CASE WHEN subscription = false THEN 1 ELSE 0 END) as unsubscriptions
FROM TIME_SERIES
LEFT JOIN YOUR_TABLE
ON TIME_SERIES.hour = date_trunc('hour', created_at) 
GROUP BY TIME_SERIES.hour

dbfiddle here