Skip to content
Advertisement

Take last hour and group it by 1 minute

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

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 hour, but group COUNT(*) per minute.

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

Advertisement

Answer

DATE_TRUNC() does this:

SELECT DATE_TRUNC('minute', createdts), COUNT(*)
FROM mytable 
WHERE createdts >= now()::date - interval '1 hour'
GROUP BY DATE_TRUNC('minute', createdts)
ORDER BY DATE_TRUNC('minute', createdts);
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement