Generuc function to generate date intervals-CodePudding

I gave the following code to several QA teams, which works fine.


with dt (dt, interv) as (
select TIMESTAMP '2022-11-01 02:37:11', numtodsinterval(5,'MINUTE') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv <
TIMESTAMP '2022-11-01 05:00:00')
select dt from dt;

The problem is VALUES are hardcoded and way too often when a person changes to put different values they make a mistake editing, which causes the code to fail.

Can this code be modified to a pipeline function or a procedure with an out parameter or a macro to prevent such problems.

I would need to pass in 2 dates(order of dates passed should not screw things up. Perhaps use least(), greatest()) an INTERVAL 'N' and a unit S=second M=MINUTE H=hour or D=Day.

Thanks to all who answer and your expertise.

CodePudding user response：

As a macro:

CREATE OR REPLACE FUNCTION gen_dt(i_from_dat IN TIMESTAMP, i_to_dat IN TIMESTAMP, i_interval IN NUMBER, i_interval_type IN VARCHAR2)
RETURN VARCHAR2
SQL_MACRO
IS
BEGIN
    RETURN q'~SELECT LEAST(i_from_dat,i_to_dat)   NUMTODSINTERVAL( (LEVEL-1)*i_interval, i_interval_type ) AS dt
    FROM DUAL
    CONNECT BY LEAST(i_from_dat,i_to_dat)    NUMTODSINTERVAL( (LEVEL-1)*i_interval, i_interval_type) < GREATEST(i_from_dat, i_to_dat)~';
END ;

(Note that you can't use WITH clause because there is a bug preventing substitution of variables inside the WITH() part)

SELECT * FROM gen_dt(SYSTIMESTAMP, SYSTIMESTAMP 1, 4, 'HOUR') ;

03/11/22 13:48:23,072872000
03/11/22 17:48:23,072872000
03/11/22 21:48:23,072872000
04/11/22 01:48:23,072872000
04/11/22 05:48:23,072872000
04/11/22 09:48:23,072872000