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:
I test it with a classic row generator using DUAL and CONNECT BY:
Which yields this output:
Yup, that works nice and simple. Now on to the fun :-)
I create a table type that my table function is going to return:
And then my table function, that accepts a REPEAT_INTERVAL parameter in the same syntax as used by DBMS_SCHEDULER:
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:
Getting all mondays, wednesdays and fridays in a period can be found like this:
Daily at 6 AM and 6 PM:
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:
You can use all the complexities that the DBMS_SCHEDULER calendaring syntax allows:
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.
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.
Comments
Post a Comment