Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table your_table (id number, start_time date, stop_time date);
insert into your_table (id, start_time, stop_time)
values (1, timestamp '2022-10-04 09:00:00', timestamp '2022-10-04 09:37:31');
1 rows affected

insert into your_table (id, start_time, stop_time)
values (2, timestamp '2022-10-04 09:52:40', timestamp '2022-10-04 11:15:00');
1 rows affected

insert into your_table (id, start_time, stop_time)
values (3, timestamp '2022-10-04 11:30:00', timestamp '2022-10-04 12:59:14');
1 rows affected

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
with p (start_time, stop_time) as (
  select cast(timestamp '2022-10-04 09:00:00' as date),
    cast(timestamp '2022-10-04 09:00:00' as date) + interval '30' minute
  from dual
  union all
  select p.stop_time, p.stop_time + interval '30' minute
  from p
  where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select * from p


START_TIME
STOP_TIME


2022-10-04 09:00:00
2022-10-04 09:30:00

2022-10-04 09:30:00
2022-10-04 10:00:00

2022-10-04 10:00:00
2022-10-04 10:30:00

2022-10-04 10:30:00
2022-10-04 11:00:00

2022-10-04 11:00:00
2022-10-04 11:30:00

2022-10-04 11:30:00
2022-10-04 12:00:00

2022-10-04 12:00:00
2022-10-04 12:30:00

2022-10-04 12:30:00
2022-10-04 13:00:00


with p (start_time, stop_time) as (
  select cast(timestamp '2022-10-04 09:00:00' as date),
    cast(timestamp '2022-10-04 09:00:00' as date) + interval '30' minute
  from dual
  union all
  select p.stop_time, p.stop_time + interval '30' minute
  from p
  where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select p.start_time,
  t.id, t.start_time, t.stop_time,
  greatest(p.start_time, t.start_time) as adj_start_time,
  least(p.stop_time, t.stop_time) as adj_stop_time,
  least(p.stop_time, t.stop_time) - greatest(p.start_time, t.start_time) as diff_days,
  round(
    (least(p.stop_time, t.stop_time) - greatest(p.start_time, t.start_time))
    * 86400
  ) as diff_secs
from p
left join your_table t
on t.start_time <= p.stop_time and t.stop_time >= p.start_time
order by p.start_time


START_TIME
ID
START_TIME
STOP_TIME
ADJ_START_TIME
ADJ_STOP_TIME
DIFF_DAYS
DIFF_SECS


2022-10-04 09:00:00
1
2022-10-04 09:00:00
2022-10-04 09:37:31
2022-10-04 09:00:00
2022-10-04 09:30:00
.0208333333333333333333333333333333333333
1800

2022-10-04 09:30:00
1
2022-10-04 09:00:00
2022-10-04 09:37:31
2022-10-04 09:30:00
2022-10-04 09:37:31
.005219907407407407407407407407407407407407
451

2022-10-04 09:30:00
2
2022-10-04 09:52:40
2022-10-04 11:15:00
2022-10-04 09:52:40
2022-10-04 10:00:00
.005092592592592592592592592592592592592593
440

2022-10-04 10:00:00
2
2022-10-04 09:52:40
2022-10-04 11:15:00
2022-10-04 10:00:00
2022-10-04 10:30:00
.0208333333333333333333333333333333333333
1800

2022-10-04 10:30:00
2
2022-10-04 09:52:40
2022-10-04 11:15:00
2022-10-04 10:30:00
2022-10-04 11:00:00
.0208333333333333333333333333333333333333
1800

2022-10-04 11:00:00
2
2022-10-04 09:52:40
2022-10-04 11:15:00
2022-10-04 11:00:00
2022-10-04 11:15:00
.0104166666666666666666666666666666666667
900

2022-10-04 11:00:00
3
2022-10-04 11:30:00
2022-10-04 12:59:14
2022-10-04 11:30:00
2022-10-04 11:30:00
0
0

2022-10-04 11:30:00
3
2022-10-04 11:30:00
2022-10-04 12:59:14
2022-10-04 11:30:00
2022-10-04 12:00:00
.0208333333333333333333333333333333333333
1800

2022-10-04 12:00:00
3
2022-10-04 11:30:00
2022-10-04 12:59:14
2022-10-04 12:00:00
2022-10-04 12:30:00
.0208333333333333333333333333333333333333
1800

2022-10-04 12:30:00
3
2022-10-04 11:30:00
2022-10-04 12:59:14
2022-10-04 12:30:00
2022-10-04 12:59:14
.0203009259259259259259259259259259259259
1754


with p (start_time, stop_time) as (
  select cast(timestamp '2022-10-04 09:00:00' as date),
    cast(timestamp '2022-10-04 09:00:00' as date) + interval '30' minute
  from dual
  union all
  select p.stop_time, p.stop_time + interval '30' minute
  from p
  where p.stop_time < timestamp '2022-10-04 13:00:00'
)
select p.start_time,
  sum(round(
    (least(p.stop_time, t.stop_time) - greatest(p.start_time, t.start_time))
    * 86400
  )) as total_secs
from p
left join your_table t
on t.start_time <= p.stop_time and t.stop_time >= p.start_time
group by p.start_time
order by p.start_time


START_TIME
TOTAL_SECS


2022-10-04 09:00:00
1800

2022-10-04 09:30:00
891

2022-10-04 10:00:00
1800

2022-10-04 10:30:00
1800

2022-10-04 11:00:00
900

2022-10-04 11:30:00
1800

2022-10-04 12:00:00
1800

2022-10-04 12:30:00
1754

START_TIME	STOP_TIME
2022-10-04 09:00:00	2022-10-04 09:30:00
2022-10-04 09:30:00	2022-10-04 10:00:00
2022-10-04 10:00:00	2022-10-04 10:30:00
2022-10-04 10:30:00	2022-10-04 11:00:00
2022-10-04 11:00:00	2022-10-04 11:30:00
2022-10-04 11:30:00	2022-10-04 12:00:00
2022-10-04 12:00:00	2022-10-04 12:30:00
2022-10-04 12:30:00	2022-10-04 13:00:00

START_TIME	ID	START_TIME	STOP_TIME	ADJ_START_TIME	ADJ_STOP_TIME	DIFF_DAYS	DIFF_SECS
2022-10-04 09:00:00	1	2022-10-04 09:00:00	2022-10-04 09:37:31	2022-10-04 09:00:00	2022-10-04 09:30:00	.0208333333333333333333333333333333333333	1800
2022-10-04 09:30:00	1	2022-10-04 09:00:00	2022-10-04 09:37:31	2022-10-04 09:30:00	2022-10-04 09:37:31	.005219907407407407407407407407407407407407	451
2022-10-04 09:30:00	2	2022-10-04 09:52:40	2022-10-04 11:15:00	2022-10-04 09:52:40	2022-10-04 10:00:00	.005092592592592592592592592592592592592593	440
2022-10-04 10:00:00	2	2022-10-04 09:52:40	2022-10-04 11:15:00	2022-10-04 10:00:00	2022-10-04 10:30:00	.0208333333333333333333333333333333333333	1800
2022-10-04 10:30:00	2	2022-10-04 09:52:40	2022-10-04 11:15:00	2022-10-04 10:30:00	2022-10-04 11:00:00	.0208333333333333333333333333333333333333	1800
2022-10-04 11:00:00	2	2022-10-04 09:52:40	2022-10-04 11:15:00	2022-10-04 11:00:00	2022-10-04 11:15:00	.0104166666666666666666666666666666666667	900
2022-10-04 11:00:00	3	2022-10-04 11:30:00	2022-10-04 12:59:14	2022-10-04 11:30:00	2022-10-04 11:30:00	0	0
2022-10-04 11:30:00	3	2022-10-04 11:30:00	2022-10-04 12:59:14	2022-10-04 11:30:00	2022-10-04 12:00:00	.0208333333333333333333333333333333333333	1800
2022-10-04 12:00:00	3	2022-10-04 11:30:00	2022-10-04 12:59:14	2022-10-04 12:00:00	2022-10-04 12:30:00	.0208333333333333333333333333333333333333	1800
2022-10-04 12:30:00	3	2022-10-04 11:30:00	2022-10-04 12:59:14	2022-10-04 12:30:00	2022-10-04 12:59:14	.0203009259259259259259259259259259259259	1754