CODEWITHSUNDEEP
sqloracle-databaselistdatetime
JB_
JB_

Reputation: 23

Oracle SQL List Intervals

I need to create new interval rows based on a start datetime column and an end datetime column.

My statement looks like this currently

select id,
startdatetime,
enddatetime
from calls

result looks like this

id       startdatetime                    enddatetime
1        01/01/2020 00:00:00              01/01/2020 04:00:00

I would like a result like this 

id       startdatetime                    enddatetime                  Intervals
1        01/01/2020 00:00:00              01/01/2020 03:00:00          01/01/2020 00:00:00
1        01/01/2020 00:00:00              01/01/2020 03:00:00          01/01/2020 01:00:00
1        01/01/2020 00:00:00              01/01/2020 03:00:00          01/01/2020 02:00:00
1        01/01/2020 00:00:00              01/01/2020 03:00:00          01/01/2020 03:00:00

Thanking you in advance p.s. I'm new to SQL

Upvotes: 0

Views: 236

Answers (2)

MT0
MT0

Reputation: 167982

You can use a recursive sub-query factoring clause to loop and incrementally add an hour:

WITH times ( id, startdatetime, enddatetime, intervals ) AS (
  SELECT id,
         startdatetime,
         enddatetime,
         startdatetime
  FROM   calls c
UNION ALL
  SELECT id,
         startdatetime,
         enddatetime,
         intervals + INTERVAL '1' HOUR
  FROM   times
  WHERE  intervals + INTERVAL '1' HOUR <= enddatetime
)
SELECT *
FROM   times;

outputs:

ID | STARTDATETIME       | ENDDATETIME         | INTERVALS          
-: | :------------------ | :------------------ | :------------------
 1 | 2020-01-01 00:00:00 | 2020-01-01 04:00:00 | 2020-01-01 00:00:00
 1 | 2020-01-01 00:00:00 | 2020-01-01 04:00:00 | 2020-01-01 01:00:00
 1 | 2020-01-01 00:00:00 | 2020-01-01 04:00:00 | 2020-01-01 02:00:00
 1 | 2020-01-01 00:00:00 | 2020-01-01 04:00:00 | 2020-01-01 03:00:00
 1 | 2020-01-01 00:00:00 | 2020-01-01 04:00:00 | 2020-01-01 04:00:00

db<>fiddle here

Upvotes: 1

Popeye
Popeye

Reputation: 35900

You can use the hierarchy query as following:

SQL> WITH CALLS (ID, STARTDATETIME, ENDDATETIME)
  2  AS ( SELECT 1,
  3          TO_DATE('01/01/2020 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),
  4          TO_DATE('01/01/2020 04:00:00', 'dd/mm/rrrr hh24:mi:ss')
  5      FROM DUAL)
  6  -- Your query starts from here
  7  SELECT
  8      ID,
  9      STARTDATETIME,
 10      ENDDATETIME,
 11      STARTDATETIME + ( COLUMN_VALUE / 24 ) AS INTERVALS
 12  FROM
 13      CALLS C
 14      CROSS JOIN TABLE ( CAST(MULTISET(
 15          SELECT LEVEL - 1
 16          FROM DUAL
 17          CONNECT BY LEVEL <= TRUNC(24 *(ENDDATETIME - STARTDATETIME))
 18      ) AS SYS.ODCINUMBERLIST) )
 19  ORDER BY INTERVALS;

        ID STARTDATETIME       ENDDATETIME         INTERVALS
---------- ------------------- ------------------- -------------------
         1 01/01/2020 00:00:00 01/01/2020 04:00:00 01/01/2020 00:00:00
         1 01/01/2020 00:00:00 01/01/2020 04:00:00 01/01/2020 01:00:00
         1 01/01/2020 00:00:00 01/01/2020 04:00:00 01/01/2020 02:00:00
         1 01/01/2020 00:00:00 01/01/2020 04:00:00 01/01/2020 03:00:00

SQL>

Cheers!!

Upvotes: 1

Related Questions