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 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?

Are you asking about something like http://stackoverflow.com/questions/7299342/what-is-the-fastest-way-to-truncate-timestamps-to-5-minutes-in-postgres ? — rfusca, Aug 28 '12 at 19:25
Do you want values for every 5 minutes (even when there are no data for the time slot), or just skip intervals without data? — Erwin Brandstetter, Aug 28 '12 at 20:37
It's worth mentioning your Pg version in questions; it can affect the features available for people when they're writing answers. — Craig Ringer, Aug 28 '12 at 23:25

Erwin Brandstetter · Answer 1 · 2021-01-10T02:59:01.140

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'

Came to learn DATE_TRUNC; learned GROUP BY 1, 2 – Anurag Priyadarshi Oct 22 '20 at 09:18 — Anurag Priyadarshi, Oct 22 '20 at 09:18

Scott Marlowe · Answer 2 · 2012-08-28T20:32:45.137

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.

date_trunc 5 minute interval in PostgreSQL

2 Answers2

Linked