Postgresql SQL GROUP BY time interval with arbitrary accuracy (down to milli seconds)

user_1612798 0
Viewed 36569

I have my measurement data stored into the following structure:

CREATE TABLE measurements(
measured_at TIMESTAMPTZ,
val INTEGER
);

I already know that using

(a) date_trunc('hour',measured_at)

AND

(b) generate_series

I would be able to aggregate my data by: 

microseconds,
milliseconds
.
.
.

But is it possible to aggregate the data by 5 minutes or let's say an arbitrary amount of seconds? Is it possible to aggregate measured data by an arbitrary multiple of seconds?

I need the data aggregated by different time resolutions to feed them into a FFT or an AR-Model in order to see possible seasonalities.

group-by postgresql
10 Answers

From PostgreSQL v14 on, you can use the date_bin function for that:

SELECT date_bin(
          INTERVAL '5 minutes',
          measured_at,
          TIMSTAMPTZ '2000-01-01'
       ),
       sum(val)
FROM measurements
GROUP BY 1;
user_64643080

The Timescale extension for PostgreSQL gives the ability to group by arbitrary time intervals. The function is called time_bucket() and has the same syntax as the date_trunc() function but takes an interval instead of a time precision as first parameter. Here you can find its API Docs. This is an example:

SELECT
  time_bucket('5 minutes', observation_time) as bucket,
  device_id,
  avg(metric) as metric_avg,
  max(metric) - min(metric) as metric_spread
FROM
  device_readings
GROUP BY bucket, device_id;

You may also take a look at the continuous aggregate views if you want the 'grouped by an interval' views be updated automatically with new ingested data and if you want to query these views on a frequent basis. This can save you a lot of resources and will make your queries a lot faster.

user_118583590
Related