PostgreSQL: Take last 3 hours and group by 5 minutes [duplicate]

varshadusa01
varshadusa01
in Academy Discussions

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

 

Visit: postgresql course 

Comments

Categories