Group by arbitrary interval

Question

I have a column that is of type timestamp. I would like to dynamically group the results by random period time (it can be 10 seconds or even 5 hours).

Supposing, I have this kind of data:

Image

If the user provides 2 hours and wants to get the max value of the air_pressure, I would like to have the first row combined with the second one. The result should look like this:

date                    | max air_pressure 
2022-11-22 00:00:00:000 | 978.81666667
2022-11-22 02:00:00:000 | 978.53
2022-11-22 04:00:00:000 | 987.23333333

and so on. As I mentioned, the period must be easy to change, because maybe he wants to group by days/seconds...

The functionality should work like function date_trunc(). But that can only group by minutes/seconds/hours, while I would like to group for arbitrary intervals.

Always process the whole table or a given time frame? What about time slots without entries? Display them .. how? Where to start time slots? At a given timestamp or the first entry (of the selection)? — Erwin Brandstetter, Dec 20 '22 at 23:48

score 0 · Accepted Answer · answered Dec 20 '22 at 23:53

Basically:

SELECT g.start_time, max(air_pressure) AS max_air_pressure
FROM   generate_series($start
                     , $end
                     , interval '15 min') g(start_time)
LEFT   JOIN tbl t ON t.date_id >= g.start_time
                 AND t.date_id <  g.start_time + interval '15 min'  -- same interval
GROUP  BY 1
ORDER  BY 1;

$start and $end are timestamps delimiting your time frame of interest.

Returns all time slots, and NULL for max_air_pressure if no matching entries are found for the time slot.

See:

Best way to count rows by arbitrary time intervals

Aside: "date_id" is an unfortunate column name for a timestamp.

Group by arbitrary interval

1 Answers1