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:
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.
CodePudding user response:
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
.