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