By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (ID, Start_Time, End_Time, Down_Mins) AS
SELECT 'ABC123',
DATE '2022-11-23' + INTERVAL '7' HOUR - NUMTODSINTERVAL(1138.55, 'MINUTE'),
DATE '2022-11-23' + INTERVAL '7' HOUR + NUMTODSINTERVAL(10232.47 - 1138.55, 'MINUTE'),
10232.47
FROM DUAL;
1 rows affected
WITH days (id, start_time, day_end, end_time, day_mins, down_mins) AS (
SELECT id,
start_time,
LEAST(TRUNC(start_time - INTERVAL '7' HOUR) + INTERVAL '31' HOUR, end_time),
end_time,
LEAST((LEAST(TRUNC(start_time - INTERVAL '7' HOUR) + INTERVAL '31' HOUR, end_time) - start_time) * 24 * 60, down_mins),
down_mins - LEAST((LEAST(TRUNC(start_time - INTERVAL '7' HOUR) + INTERVAL '31' HOUR, end_time) - start_time) * 24 * 60, down_mins)
FROM table_name
UNION ALL
SELECT id,
day_end,
LEAST(day_end + INTERVAL '24' HOUR, end_time),
end_time,
LEAST((LEAST(day_end + INTERVAL '24' HOUR, end_time) - day_end) * 24 * 60, down_mins),
down_mins - LEAST((LEAST(day_end + INTERVAL '24' HOUR, end_time) - day_end) * 24 * 60, down_mins)
FROM days
WHERE day_end < end_time
AND down_mins > 0
)
SEARCH DEPTH FIRST BY id, start_time SET order_id
SELECT id,
start_time,
day_end AS end_time,
day_mins AS down_mins
FROM days;
| ID | START_TIME | END_TIME | DOWN_MINS |
|---|---|---|---|
| ABC123 | 2022-11-22 12:01:27 | 2022-11-23 07:00:00 | 1138.55 |
| ABC123 | 2022-11-23 07:00:00 | 2022-11-24 07:00:00 | 1440 |
| ABC123 | 2022-11-24 07:00:00 | 2022-11-25 07:00:00 | 1440 |
| ABC123 | 2022-11-25 07:00:00 | 2022-11-26 07:00:00 | 1440 |
| ABC123 | 2022-11-26 07:00:00 | 2022-11-27 07:00:00 | 1440 |
| ABC123 | 2022-11-27 07:00:00 | 2022-11-28 07:00:00 | 1440 |
| ABC123 | 2022-11-28 07:00:00 | 2022-11-29 07:00:00 | 1440 |
| ABC123 | 2022-11-29 07:00:00 | 2022-11-29 14:33:55 | 453.916666666666666666666666666666666667 |