sqldatabasepostgresqlgroup-bygenerate-series

Group by arbitrary interval

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.

Solution

  • 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:

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