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

Question

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

Answer 1

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