Home > Enterprise >  Split row data base on timestamp SQL Oracle
Split row data base on timestamp SQL Oracle

Time:10-10

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.

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:

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

  • Related