Oracle 23c

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';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE table_name (ID, STARTDATE, ENDDATE, Status) AS
SELECT 1, TIMESTAMP '2024-02-01 08:00:00.000', TIMESTAMP '2024-02-02 10:30:00.000', 'a' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2024-02-02 10:30:00.000', TIMESTAMP '2024-02-05 14:20:00.000', 'b' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '2024-02-05 14:20:00.000', TIMESTAMP '2024-02-07 08:15:00.000', 'c' FROM DUAL;
3 rows affected

WITH days (id, startdate, enddate, maxdate, status) AS (
  SELECT id,
         startdate,
         LEAST(
           CAST(TRUNC(startdate) + INTERVAL '1' DAY AS TIMESTAMP),
           enddate
         ),
         enddate,
         status
  FROM   table_name
UNION ALL
  SELECT id,
         enddate,
         LEAST(
           enddate + INTERVAL '1' DAY,
           maxdate
         ),
         maxdate,
         status
  FROM   days
  WHERE  enddate < maxdate
)
SEARCH DEPTH FIRST BY id, status SET order_id
SELECT id, startdate, enddate, status
FROM   days


ID
STARTDATE
ENDDATE
STATUS


1
2024-02-01 08:00:00.000000000
2024-02-02 00:00:00.000000000
a

1
2024-02-02 00:00:00.000000000
2024-02-02 10:30:00.000000000
a

1
2024-02-02 10:30:00.000000000
2024-02-03 00:00:00.000000000
b

1
2024-02-03 00:00:00.000000000
2024-02-04 00:00:00.000000000
b

1
2024-02-04 00:00:00.000000000
2024-02-05 00:00:00.000000000
b

1
2024-02-05 00:00:00.000000000
2024-02-05 14:20:00.000000000
b

1
2024-02-05 14:20:00.000000000
2024-02-06 00:00:00.000000000
c

1
2024-02-06 00:00:00.000000000
2024-02-07 00:00:00.000000000
c

1
2024-02-07 00:00:00.000000000
2024-02-07 08:15:00.000000000
c


SELECT id,
       GREATEST(
         startdate,
         CAST(TRUNC(startdate) + day AS TIMESTAMP)
       ) AS startdate,
       LEAST(
         enddate,
         CAST(TRUNC(startdate) + day + 1 AS TIMESTAMP)
       ) AS enddate,
       status
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT LEVEL - 1 AS day
         FROM   DUAL
         CONNECT BY LEVEL <= TRUNC(enddate) - TRUNC(startdate) + 1
       )


ID
STARTDATE
ENDDATE
STATUS


1
2024-02-01 08:00:00.000000000
2024-02-02 00:00:00.000000000
a

1
2024-02-02 00:00:00.000000000
2024-02-02 10:30:00.000000000
a

1
2024-02-02 10:30:00.000000000
2024-02-03 00:00:00.000000000
b

1
2024-02-03 00:00:00.000000000
2024-02-04 00:00:00.000000000
b

1
2024-02-04 00:00:00.000000000
2024-02-05 00:00:00.000000000
b

1
2024-02-05 00:00:00.000000000
2024-02-05 14:20:00.000000000
b

1
2024-02-05 14:20:00.000000000
2024-02-06 00:00:00.000000000
c

1
2024-02-06 00:00:00.000000000
2024-02-07 00:00:00.000000000
c

1
2024-02-07 00:00:00.000000000
2024-02-07 08:15:00.000000000
c

ID	STARTDATE	ENDDATE	STATUS
1	2024-02-01 08:00:00.000000000	2024-02-02 00:00:00.000000000	a
1	2024-02-02 00:00:00.000000000	2024-02-02 10:30:00.000000000	a
1	2024-02-02 10:30:00.000000000	2024-02-03 00:00:00.000000000	b
1	2024-02-03 00:00:00.000000000	2024-02-04 00:00:00.000000000	b
1	2024-02-04 00:00:00.000000000	2024-02-05 00:00:00.000000000	b
1	2024-02-05 00:00:00.000000000	2024-02-05 14:20:00.000000000	b
1	2024-02-05 14:20:00.000000000	2024-02-06 00:00:00.000000000	c
1	2024-02-06 00:00:00.000000000	2024-02-07 00:00:00.000000000	c
1	2024-02-07 00:00:00.000000000	2024-02-07 08:15:00.000000000	c