Oracle 21c

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

CREATE TABLE table_name (ID, start_dt, stop_dt) AS
SELECT 5, DATE '2022-04-10' + INTERVAL '09:00:00' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '09:37:31' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' + INTERVAL '09:52:40' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '11:15:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 5, DATE '2022-04-10' + INTERVAL '11:30:00' HOUR TO SECOND, DATE '2022-04-10' + INTERVAL '12:59:14' HOUR TO SECOND FROM DUAL;;
3 rows affected

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
WITH bounds (id, min_start, max_stop) AS (
  SELECT id,
         TRUNC(CAST(MIN(start_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MIN(start_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '30' MINUTE
           ELSE INTERVAL '0' MINUTE
           END,
         TRUNC(CAST(MAX(stop_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MAX(stop_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '60' MINUTE
           ELSE INTERVAL '30' MINUTE
           END
  FROM   table_name
  GROUP BY id
),
half_hours (id, start_dt) AS (
  SELECT b.id, t.start_dt
  FROM   bounds b
         CROSS JOIN LATERAL(
           SELECT min_start + (LEVEL - 1) * INTERVAL '30' MINUTE AS start_dt
           FROM   DUAL
           CONNECT BY min_start + LEVEL * INTERVAL '30' MINUTE <= max_stop
         ) t
)
SELECT * FROM half_hours;


ID
START_DT


5
2022-04-10 09:00:00

5
2022-04-10 09:30:00

5
2022-04-10 10:00:00

5
2022-04-10 10:30:00

5
2022-04-10 11:00:00

5
2022-04-10 11:30:00

5
2022-04-10 12:00:00

5
2022-04-10 12:30:00


WITH bounds (id, min_start, max_stop) AS (
  SELECT id,
         TRUNC(CAST(MIN(start_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MIN(start_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '30' MINUTE
           ELSE INTERVAL '0' MINUTE
           END,
         TRUNC(CAST(MAX(stop_dt) AS TIMESTAMP), 'HH')
         + CASE
           WHEN EXTRACT(MINUTE FROM CAST(MAX(stop_dt) AS TIMESTAMP)) >= 30
           THEN INTERVAL '60' MINUTE
           ELSE INTERVAL '30' MINUTE
           END
  FROM   table_name
  GROUP BY id
),
half_hours (id, start_dt) AS (
  SELECT b.id, t.start_dt
  FROM   bounds b
         CROSS JOIN LATERAL(
           SELECT min_start + (LEVEL - 1) * INTERVAL '30' MINUTE AS start_dt
           FROM   DUAL
           CONNECT BY min_start + LEVEL * INTERVAL '30' MINUTE <= max_stop
         ) t
)
SELECT h.id,
       h.start_dt,
       h.start_dt + INTERVAL '30' MINUTE AS stop_dt,
       ROUND(
         SUM(
           LEAST(h.start_dt + INTERVAL '30' MINUTE, t.stop_dt)
           - GREATEST(h.start_dt, t.start_dt)
         ) * 24 * 60 * 60
       ) AS seconds
FROM   half_hours h
       LEFT OUTER JOIN table_name t
       ON (    h.id = t.id
           AND t.start_dt < h.start_dt + INTERVAL '30' MINUTE
           AND h.start_dt < t.stop_dt)
GROUP BY
       h.id,
       h.start_dt


ID
START_DT
STOP_DT
SECONDS


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

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

5
2022-04-10 10:00:00
2022-04-10 10:30:00
1800

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

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

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

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

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

ID	START_DT
5	2022-04-10 09:00:00
5	2022-04-10 09:30:00
5	2022-04-10 10:00:00
5	2022-04-10 10:30:00
5	2022-04-10 11:00:00
5	2022-04-10 11:30:00
5	2022-04-10 12:00:00
5	2022-04-10 12:30:00

ID	START_DT	STOP_DT	SECONDS
5	2022-04-10 09:00:00	2022-04-10 09:30:00	1800
5	2022-04-10 09:30:00	2022-04-10 10:00:00	891
5	2022-04-10 10:00:00	2022-04-10 10:30:00	1800
5	2022-04-10 10:30:00	2022-04-10 11:00:00	1800
5	2022-04-10 11:00:00	2022-04-10 11:30:00	900
5	2022-04-10 11:30:00	2022-04-10 12:00:00	1800
5	2022-04-10 12:00:00	2022-04-10 12:30:00	1800
5	2022-04-10 12:30:00	2022-04-10 13:00:00	1754