date_trunc 5 minute interval in PostgreSQL [duplicate]

Question

Possible Duplicate:
What is the fastest way to truncate timestamps to 5 minutes in Postgres?
Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)

I want to aggregate data at 5 minute intervals in PostgreSQL. If I use the date_trunc() function, I can aggregate data at an hourly, monthly, daily, weekly, etc. interval but not a specific interval like 5 minute or 5 days.

select date_trunc('hour', date1), count(*) from table1 group by 1; 

How can we achieve this in PostgreSQL?

Answer 1

SELECT date_trunc('hour', date1) AS hour_stump      , (extract(minute FROM date1)::int / 5) AS min5_slot      , count(*) FROM   table1 GROUP  BY 1, 2 ORDER  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:

• PostgreSQL: running count of rows for a query 'by minute'