Alex Rivera | Logout

date_trunc 5 minute interval in PostgreSQL

Asked 2012-08-28T19:05:14.083
47

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?

sql postgresql datetime aggregate-functions
Edit
Report

1 Answer

2

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.

answered 2012-08-28T19:55:43.920

Your Answer