date_trunc 5 minute interval in PostgreSQL [duplicate]
SELECT date_trunc('hour', date1) AS hour_stump , (extract(minute FROM date1)::int / 5) AS min5_slot , count(*)FROM table1GROUP BY 1, 2ORDER BY 1, 2;
You could GROUP BY
two columns: a timestamp truncated to the hour and a 5-minute-slot.
The example produces slots 0 - 11
. Add 1
if you prefer 1 - 12
.
I cast the result of extract()
to integer, so the division / 5
truncates fractional digits. The result:
minute 0 - 4 -> slot 0
minute 5 - 9 -> slot 1
etc.
This query only returns values for those 5-minute slots where values are found. If you want a value for every slot or if you want a running sum over 5-minute slots, consider this related answer:
Here's a simple query you can either wrap in a function or cut and paste all over the place:
select now()::timestamp(0), (extract(epoch from now()::timestamptz(0)-date_trunc('d',now()))::int)/60;
It'll give you the current time, and a number from 0 to the n-1 where n=60 here. To make it every 5 minutes, make that number 300 and so on. It groups by the seconds since the start of the day. To make it group by seconds since year begin, hour begin, or whatever else, change the 'd' in the date_trunc.