Home > Blockchain >  Collapse rows into buckets in PostgreSQL
Collapse rows into buckets in PostgreSQL

Time:11-20

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.

I've managed to get this far: screen capture from demo link below

Demo

  • Related