I have the following dataset in PostgreSQL:
row_id, wind_speed_category, timestamp
1, 3, 2021-01-20 10:00:01
2, 3, 2021-01-21 11:00:01
3, 3, 2021-01-21 12:00:01
4, 4, 2021-01-21 14:00:01
5, 4, 2021-01-23 10:00:01
6, 3, 2021-02-22 10:00:01
I would like to collapse the rows into "running" buckets, so getting a result somewhat like:
wind_speed_category, interval_seconds
3, 86400
4, 2764800
3, 0
The interval_seconds
is based upon the interval of timestamp in first row in the bucket
and last row 1
in bucket.