Friday, December 19, 2014

DATE row generator with DBMS_SCHEDULER

A recent question on the Oracle-L mailing list was about generating dates in a given period. If you had a string like 'MWF', all Mondays, Wednesdays and Fridays between the from- and to-dates should be generated.

That reminded me of a quiz on plsqlchallenge.com I did on using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING. I had created a table function to test calendar expressions - that might be useful for something similar to what was asked on the mailing list.

So here's a way to generate rows of DATEs using DBMS_SCHEDULER calendaring syntax.


I start by setting my session NLS_DATE_FORMAT - just so that the following queries show clearly what date values has been generated:

alter session set nls_date_format='YYYY-MM-DD Dy HH24:MI:SS'
/

I test it with a classic row generator using DUAL and CONNECT BY:

select DATE '2015-01-01' + level - 1 the_date
  from dual
connect by level <= 7
/

Which yields this output:

THE_DATE
-----------------------
2015-01-01 Thu 00:00:00
2015-01-02 Fri 00:00:00
2015-01-03 Sat 00:00:00
2015-01-04 Sun 00:00:00
2015-01-05 Mon 00:00:00
2015-01-06 Tue 00:00:00
2015-01-07 Wed 00:00:00

Yup, that works nice and simple. Now on to the fun :-)

I create a table type that my table function is going to return:

create type t_date_table as table of date
/

And then my table function, that accepts a REPEAT_INTERVAL parameter in the same syntax as used by DBMS_SCHEDULER:

create or replace function date_generator(
   p_repeat_interval in varchar2
 , p_start_date      in date
 , p_end_date        in date
)
   return t_date_table pipelined
is
   l_date_after   date;
   l_next_date    date;
begin
   l_date_after := p_start_date - 1;
   loop
       dbms_scheduler.evaluate_calendar_string(
          calendar_string   => p_repeat_interval
        , start_date        => p_start_date
        , return_date_after => l_date_after
        , next_run_date     => l_next_date
       );
       exit when l_next_date > p_end_date;
       pipe row (l_next_date);
       l_date_after := l_next_date;
   end loop;
end date_generator;
/

It works simply by repeated calls to DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING until the end date has been reached.

So I can get the same result as the above CONNECT BY query using this calendaring expression:

select dg.column_value the_date
  from table( date_generator(
            'FREQ=DAILY'
          , DATE '2015-01-01'
          , DATE '2015-01-07'
       )) dg
/

THE_DATE
-----------------------
2015-01-01 Thu 00:00:00
2015-01-02 Fri 00:00:00
2015-01-03 Sat 00:00:00
2015-01-04 Sun 00:00:00
2015-01-05 Mon 00:00:00
2015-01-06 Tue 00:00:00
2015-01-07 Wed 00:00:00

Getting all mondays, wednesdays and fridays in a period can be found like this:

select dg.column_value the_date
  from table( date_generator(
            'FREQ=WEEKLY; BYDAY=MON,WED,FRI'
          , DATE '2015-01-01'
          , DATE '2015-01-14'
       )) dg
/

THE_DATE
-----------------------
2015-01-02 Fri 00:00:00
2015-01-05 Mon 00:00:00
2015-01-07 Wed 00:00:00
2015-01-09 Fri 00:00:00
2015-01-12 Mon 00:00:00
2015-01-14 Wed 00:00:00

Daily at 6 AM and 6 PM:

select dg.column_value the_date
  from table( date_generator(
            'FREQ=DAILY; BYHOUR=6,18'
          , DATE '2015-01-01'
          , DATE '2015-01-04'
       )) dg
/

THE_DATE
-----------------------
2015-01-01 Thu 06:00:00
2015-01-01 Thu 18:00:00
2015-01-02 Fri 06:00:00
2015-01-02 Fri 18:00:00
2015-01-03 Sat 06:00:00
2015-01-03 Sat 18:00:00

Notice how the above was specified 2015-01-04 as end date, but no results were given for 2015-01-04? Well, that's because the end date was at midnight. When using time in the expressions I would also need to be aware of time in the start and end date parameters:

select dg.column_value the_date
  from table( date_generator(
            'FREQ=DAILY; BYHOUR=6,18'
          , DATE '2015-01-01'
          , to_date('2015-01-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
       )) dg
/

THE_DATE
-----------------------
2015-01-01 Thu 06:00:00
2015-01-01 Thu 18:00:00
2015-01-02 Fri 06:00:00
2015-01-02 Fri 18:00:00
2015-01-03 Sat 06:00:00
2015-01-03 Sat 18:00:00
2015-01-04 Sun 06:00:00
2015-01-04 Sun 18:00:00

You can use all the complexities that the DBMS_SCHEDULER calendaring syntax allows:

select dg.column_value the_date
  from table( date_generator(
            'FREQ=MONTHLY; INTERVAL=2; BYDAY=TUE; BYSETPOS=2; BYHOUR=18; BYMINUTE=30'
          , DATE '2015-02-01'
          , DATE '2015-12-31'
       )) dg
/

THE_DATE
-----------------------
2015-02-10 Tue 18:30:00
2015-04-14 Tue 18:30:00
2015-06-09 Tue 18:30:00
2015-08-11 Tue 18:30:00
2015-10-13 Tue 18:30:00
2015-12-08 Tue 18:30:00

That expression is for 18:30 o'clock on the second tuesday of every other month (as I start with february, that then becomes even months. (Very important schedule, as those are the times that my Beer Enthusiast Association usually meets ;-)

The table function method using DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING can be helpful to generate rows of DATEs when you need some rules about which dates to generate. But if you just need a simple list of all dates between start and end, that would be overkill and the SELECT FROM DUAL method will be much more efficient.

No comments:

Post a Comment