Split row data base on timestamp SQL Oracle

Good day everyone. I have a table as below. Duration is the time from current state to next state.

Timestamp	State	Duration(minutes)
10/9/2022 8:50:00 AM	A	35
10/9/2022 9:25:00 AM	B	10
10/9/2022 9:35:00 AM	C	…

How do I split data at 9:00 AM of each day like below:

Timestamp	State	Duration(minutes)
10/9/2022 8:50:00 AM	A	10
10/9/2022 9:00:00 AM	A	25
10/9/2022 9:25:00 AM	B	10
10/9/2022 9:35:00 AM	C	…

Thank you.

Answer

Use a row-generator function to generate extra rows when the timestamp is before 09:00 and the next timestamp is after 09:00 (and calculate the diff value rather than storing it in the table):

SELECT l.ts AS timestamp,
       t.state,
       ROUND((l.next_ts - l.ts) * 24 * 60, 2) As diff
FROM   (
         SELECT timestamp,
                LEAD(timestamp) OVER (ORDER BY timestamp) AS next_timestamp,
                state
         FROM   table_name
       ) t
       CROSS APPLY (
         SELECT GREATEST(
                  t.timestamp,
                  TRUNC(t.timestamp - INTERVAL '9' HOUR) + INTERVAL '9' HOUR + LEVEL - 1
                ) AS ts,
                LEAST(
                  t.next_timestamp,
                  TRUNC(t.timestamp - INTERVAL '9' HOUR) + INTERVAL '9' HOUR + LEVEL
                ) AS next_ts
         FROM   DUAL
         CONNECT BY
                TRUNC(t.timestamp - INTERVAL '9' HOUR) + INTERVAL '9' HOUR + LEVEL - 1 < t.next_timestamp
       ) l;

Which, for your sample data:

CREATE TABLE table_name (Timestamp, State) AS
SELECT DATE '2022-10-09' + INTERVAL '08:50' HOUR TO MINUTE, 'A' FROM DUAL UNION ALL
SELECT DATE '2022-10-09' + INTERVAL '09:25' HOUR TO MINUTE, 'B' FROM DUAL UNION ALL
SELECT DATE '2022-10-09' + INTERVAL '09:35' HOUR TO MINUTE, 'C' FROM DUAL UNION ALL
SELECT DATE '2022-10-12' + INTERVAL '09:35' HOUR TO MINUTE, 'D' FROM DUAL;

Outputs:

TIMESTAMP	STATE	DIFF
2022-10-09 08:50:00	A	10
2022-10-09 09:00:00	A	25
2022-10-09 09:25:00	B	10
2022-10-09 09:35:00	C	1405
2022-10-10 09:00:00	C	1440
2022-10-11 09:00:00	C	1440
2022-10-12 09:00:00	C	35
2022-10-12 09:35:00	D	null

fiddle