CODEWITHSUNDEEP
sqloracle-databasedatetimerecursioncommon-table-expression
Beefstu
Beefstu

Reputation: 857

Oracle recursive CTE for date with interval

I'm trying to create a recursive CTE, which generates a date/time for every 10 minutes and stops at midnight but I am struggling with the syntax and can't seem to get it to work.

I would be very appreciative to anyone who can help me out. Thanks in advance to all who answer.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';


with date_rows 
(( SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS') as start_date  FROM DUAL ) 
union all
select start_date+interval '10' minute
 from date_rows
where start_date < 
TRUNC(start_date) + INTERVAL '1' DAY)
select * from date_rows;

Upvotes: 1

Views: 1578

Answers (2)

MT0
MT0

Reputation: 167972

You can use:

WITH inputs ( value ) AS (
  SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
  FROM   DUAL
),
date_rows ( start_date, end_date ) AS (
  SELECT value,
         TRUNC(value) + INTERVAL '1' DAY
  FROM   inputs
UNION ALL
  SELECT start_date + INTERVAL '10' MINUTE,
         end_date
  FROM   date_rows
  WHERE  start_date + INTERVAL '10' MINUTE < end_date
)
SELECT start_date
FROM   date_rows;

or, if you want to duplicate the input value then you can use:

WITH date_rows ( start_date ) AS (
  SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
  FROM   DUAL
UNION ALL
  SELECT start_date + INTERVAL '10' MINUTE
  FROM   date_rows
  WHERE  start_date + INTERVAL '10' MINUTE
           < TRUNC(TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS'))
             + INTERVAL '1' DAY
)
SELECT *
FROM   date_rows;

Note: At each iteration of the recursive sub-query, start_date is the previous value so you need to check if start_date + INTERVAL '10' MINUTE is before midnight (rather than just start_date) otherwise you will have the final row after midnight.

db<>fiddle here

What was wrong with your code:

  1. For a non-recursive sub-query factoring clause, the syntax is:

    WITH query_alias AS (

    or

    WITH query_alias (column_alias) AS (

    You were missing the AS keyword.

    However, for a recursive sub-query factoring clause, you need to use the second version and specify the column aliases.

  2. The () brackets around the first SELECT are redundant (but won't cause an error).

  3. start_date < TRUNC(start_date) + INTERVAL '1' DAY) is always going to be true, even if the date goes past the midnight boundary; so the query will recurse infinitely.

db<>fiddle here

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142705

Something like this, I presume.

SQL> with date_rows (datum) as
  2    (select cast (to_date('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS') as date)
  3     from dual
  4     union all
  5     select cast (datum + interval '10' minute as date)
  6     from date_rows
  7     where datum < trunc(to_date('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')) + interval '1' day
  8    )
  9  select * from date_rows;

DATUM
-----------------
08202021 18:30:25
08202021 18:40:25
08202021 18:50:25
08202021 19:00:25
08202021 19:10:25
08202021 19:20:25
08202021 19:30:25
08202021 19:40:25
08202021 19:50:25
08202021 20:00:25
08202021 20:10:25
<snip>    
08202021 23:20:25
08202021 23:30:25
08202021 23:40:25
08202021 23:50:25
08212021 00:00:25

34 rows selected.

SQL>

Upvotes: 1

Related Questions