CODEWITHSUNDEEP
sqlpostgresqldate-rangegenerate-series
matthewr
matthewr

Reputation: 324

Postgres split time ranges into 1 minute slots, without rounding boundaries

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

Upvotes: 1

Views: 106

Answers (1)

Zegarek
Zegarek

Reputation: 26347

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

Upvotes: 3

Related Questions