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

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

Thank you.

CodePudding user response：

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:

fiddle