Oracle generating time INTERVAL without crossing next day-CodePudding

I also have a way to generate dates with times using an internal. I know I can use CONNECT by level <=N but that would require me manually editing N for each interval, which I'm trying to avoid.

What I like to do is populate each date in a range with a series of times based on an interval that starts at midnight and doesn't go past 23:59:59 for each date. if the time goes past midnight I want to start incrementing for the next day at midnight..

For example, below stops incrementing at 01112022 03:20:00 when I would like it to stop at 01112022 23:50:00


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


select to_date ( 
'01-11-2022','mm-dd-yyyy' )    numtodsinterval(rownum*10,'MINUTE')
    from dual
    connect by level <= 20;

01112022 00:10:00
01112022 00:20:00
01112022 00:30:00
01112022 00:40:00
01112022 00:50:00
01112022 01:00:00
01112022 01:10:00
01112022 01:20:00
01112022 01:30:00
01112022 01:40:00
01112022 01:50:00
01112022 02:00:00
01112022 02:10:00
01112022 02:20:00
01112022 02:30:00
01112022 02:40:00
01112022 02:50:00
01112022 03:00:00
01112022 03:10:00
01112022 03:20:00

CodePudding user response：

Instead of trying to specify N with an exact number of intervals, you can use a start date/end date logic to generate the intervals.

    SELECT DATE '2022-01-11'   NUMTODSINTERVAL (LEVEL * 10, 'MINUTE')
      FROM DUAL
CONNECT BY DATE '2022-01-11'   NUMTODSINTERVAL (LEVEL * 10, 'MINUTE') < DATE '2022-01-12';

CodePudding user response：

This is a nice use case for a recursive CTE.

with dt (dt, interv) as (
select date '2022-01-11', numtodsinterval(10,'MINUTE') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv <  trunc(dt.dt) 1)
select dt from dt;

DT                 
-------------------
11.01.2022 00:00:00
11.01.2022 00:10:00
11.01.2022 00:20:00
.....
11.01.2022 23:50:00

Note that I added the interval as a column, so you need not the repeat it twice in the query.

The start date and interval are used only once in the query, so you may them easily configure.