clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798680 fiddles created (41875 in the last week).

create table the_table ( id int primary key, "start" timestamp, "end" timestamp ); insert into the_table values (1, '2022-05-07 06:00', '2022-05-09 12:00'), (2, '2022-05-08 04:00', '2022-05-12 19:30'); select t.id, case when t.start::date = x.dt::date then t.start else date_trunc('day', x.dt) end as "start", case when t.end::date = x.dt::date then t.end else (x.dt::date + 1)::timestamp end as "end" from the_table t cross join generate_series(t.start, t.end, interval '1 day') as x(dt) order by t.id, "start"
2 rows affected
id start end
1 2022-05-07 06:00:00 2022-05-08 00:00:00
1 2022-05-08 00:00:00 2022-05-09 00:00:00
1 2022-05-09 00:00:00 2022-05-09 12:00:00
2 2022-05-08 04:00:00 2022-05-09 00:00:00
2 2022-05-09 00:00:00 2022-05-10 00:00:00
2 2022-05-10 00:00:00 2022-05-11 00:00:00
2 2022-05-11 00:00:00 2022-05-12 00:00:00
2 2022-05-12 00:00:00 2022-05-12 19:30:00
 hidden batch(es)