How to Aggregate 15 minute Time series into 1 hour intervals in Postgres

In my PostgreSQL database I have a very large (over 40 mil) row table that represents traffic counts for various locations by 15 minute interval timestamps (contained in the “starttime” column). I want to bin these intervals into hourly intervals to reduce the number of rows.

The format is “timestamp without time zone” so that each record looks like this:

2020-04-26 01:00:00
2020-04-26 01:15:00
2020-04-26 01:30:00
2020-04-26 01:45:00
etc…

What I want to do is take all these 15-min records and “roll them up” into 1 hour intervals so that 01:00:00– 01:45:00 would equal 01:00:00 (1AM)

My question is similar to this one but the answer is with regard to MySQL and I am not sure if this syntax is appropriate for a Postgres application.

Is there a way I can handle this with an SQL script within Postgres?

Answer

You would use date_trunc(). For instance to get the count by hour:

select date_trunc('hour', ts), count(*)
from t
group by date_trunc('hour', ts)

Advertisement

Answer