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 |
Cross join
each row to a generate_series()
to spawn the 1-minute slots, use greatest()
and least()
to keep the non-aligned start and end timestamps.
demo at db<>fiddle
select greatest(slot,start_datetime) as start_datetime
, least(slot+'1min',end_datetime) as end_datetime
, id
from test
cross join lateral generate_series( date_trunc('minute',start_datetime)
,end_datetime
,'1min') as slot;
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 |