Take last 3 hours and group by 5 minutes

Question

I was wondering if you can help me write a query that should just SELECT count(*) but only include data from last 3 hours and group it by 5 minutes.

So I have a table that has a createdts so I have the date there. I just want to see how many entries I have in the last 3 hours, but group COUNT(*) per 5 minutes.

SELECT COUNT(*) 
FROM mytable 
WHERE createdts >= now()::date - interval '3 hour'
GROUP BY 'every 5 minutes'

Also, what's really important is that the Count(*)'s that are null, get defaulted to 0. I have many windows of time where the data will be null, and having it default to 0 saves a lot of headache later

Answer 1

Use generate_series():

SELECT gs.t, COUNT(t.createdts)
FROM GENERATE_SERIES(now()::date - interval '3 hour', now()::date, interval '5 minute') gs(t)
     mytable t
     ON t.createdts >= gs.t AND
        t.createdts < gs.t + interval '5 minute'
GROUP BY gs.t;