I have a postgres table with timestamp columns:
start_datetime | end_datetime | duration | id |
---|---|---|---|
2021-10-17 03:13:00 | 2021-10-17 03:15:02 | 302 | 6214550 |
2021-10-17 03:15:02 | 2021-10-17 03:17:03 | 4,021 | 6214551 |
which i need to split out in to buckets constrained to either the end of the minute or the end_datetime
as:
start_datetime | end_datetime | id |
---|---|---|
2021-10-17 03:13:00 | 2021-10-17 03:14:00 | 6214550 |
2021-10-17 03:14:00 | 2021-10-17 03:15:00 | 6214550 |
2021-10-17 03:15:00 | 2021-10-17 03:15:02 | 6214550 |
2021-10-17 03:15:02 | 2021-10-17 03:16:00 | 6214551 |
2021-10-17 03:16:00 | 2021-10-17 03:17:00 | 6214551 |
2021-10-17 03:17:00 | 2021-10-17 03:17:03 | 6214551 |
2
Answers
Schema (PostgreSQL v15)
Query #1
View on DB Fiddle
Cross join
each row to agenerate_series()
to spawn the 1-minute slots, usegreatest()
andleast()
to keep the non-aligned start and end timestamps.demo at db<>fiddle