[SQL] Fill missing date gaps in SQL query on PostgreSQL

I have a table messages that looks like:

status created_at updated_at
sent timestamp timestamp
queued timestamp timestamp
failed timestamp timestamp

I want a query that outputs values in a way that is easy to chart, I came up with this query:

SELECT date_trunc('hour', created_at) as hour_created, count(created_at), status
FROM messages
WHERE created_at >= current_timestamp - interval '1 day'
GROUP BY (status, hour_created)


That query outputs the following:

hour_created count status
2021-10-11 08:00:00-04 10 sent
2021-10-11 09:00:00-04 95 queued
2021-10-11 10:00:00-04 174 sent
2021-10-11 12:00:00-04 1 sent
2021-10-11 13:00:00-04 1 queued
2021-10-11 13:00:00-04 2 sent
2021-10-11 14:00:00-04 1 sent
2021-10-11 18:00:00-04 2 sent

Notice the gaps in the hours, I want to fill those with every possible status (queued, failed and sent) and a zero

Continue reading...