date_trunc 5 minute interval in PostgreSQL

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 a 5 minute interval 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;

Does anyone know how we can achieve this in PostgreSQL?

Any help is appreciated.

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'