CODEWITHSUNDEEP
sqlpostgresql
vukojevicf
vukojevicf

Reputation: 827

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'

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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);

Upvotes: 2

Related Questions