A rather common question in the Oracle community goes along the lines of this: “How do I generate a list of dates?”, with variations such as needing only working days, excluding specific dates, etc..
If there is a standard method for this it tends to go along the lines of this:
- Generate a rather vanilla list of dates using a CONNECT BY clause on the DUAL table
- Filter the results based on day of week or other criteria.
Other more sophisticated variations are also out there in the wild, and for the most part there is nothing technically wrong with them if they are fast and accurate and easily understood.
At the upper end of complexity it can be rather tricky to develop an accurate query. Here then is an alternative method that uses DBMS_Scheduler to define a calendar. DBMS_Scheduler has a sophisticated calendaring syntax described in the 11gR2 documentation here (with some interesting examples that are definately worth browsing here), that would do a very good job of implementing some complex list of date requirements. Although it would probably be my first choice for very complex list generation it also does such a decent job of simple list generation that I’d be inclined to consider it for those as well.
The method is built around the DBMS_Scheduler subprogram Evaluate_Calendar_String. This subprogram returns as one of its parameters a single date (well actually a TIMESTAMP WITH TIME ZONE) which is the “next” date in a calendar calculated from the following three input parameters::
- A calendar string that defines the repeating interval of the list of dates. Here is the point where you would specify that you are interested only in the last-but-one day of every month as “FREQ=MONTHLY; BYMONTHDAY=-2;” for example.
- A start date that gives the earliest date from which the calendar string is to be evaluated. For example, from today’s date, or from the beginning of this year. The start date may be redundant depending on how complete the specification of the calendar string is, and to me it seems like a hangover from the days of DBMS_Job in which you specify a start point and a method for calculating the next date based on it. Anyway, with a complete calendar specification the start date can be ignored. The example above is not complete because it does not specify the time of day for the calendar dates. This could be inferred from the start date if it were something like “2010-01-01 00:00:00″, but if you did not specify a start date then the calendar will inherit the time of day that you executed the program. It’s safer in my opinion to specify a complete calendar string such as ‘FREQ=MONTHLY; BYMONTHDAY=-2;BYHOUR=0;BYMINUTE=0;BYSECOND=0′
- A date from which the next date in the calendar will be calculated. Typically this date would be the previous date in the list of calendar dates.
So with a complete calendar specification the Evaluate_Calendar_String program comes down to two elements: a calendar string, and a date to return the next calendar date after. Pretty simple.
We can therefore use the program as the basis for a pipe-lined function that accepts a calendar string and an initial date to start calculating subsequent calendar dates from, and which returns the desired list of dates.
Here is a hastily cobbled-together version.
create or replace type timestamp_table_type
is
table of timestamp;
/
create or replace function
list_of_dates (
calendar_string varchar2,
start_date TIMESTAMP WITH TIME ZONE,
stop_date TIMESTAMP WITH TIME ZONE)
return
timestamp_table_type
pipelined
is
l_return_date_after TIMESTAMP WITH TIME ZONE := start_date - interval '1' second;
l_next_run_date TIMESTAMP WITH TIME ZONE;
begin
loop
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
calendar_string => list_of_dates.calendar_string,
start_date => list_of_dates.start_date,
return_date_after => l_return_date_after,
next_run_date => l_next_run_date);
exit when list_of_dates.l_next_run_date > coalesce(list_of_dates.stop_date,date '9999-12-31');
pipe row (list_of_dates.l_next_run_date);
list_of_dates.l_return_date_after := list_of_dates.l_next_run_date;
end loop;
end;
/
Here it is in action then:
“Give me a list of days for this year”
select *
from
table(
list_of_dates(
'FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
trunc(sysdate,'yyyy'),
add_months(trunc(sysdate,'yyyy'),12)-1));
“Give me a list of the last-but-one day for each month from now to the end of the year”
select *
from
table(
list_of_dates(
'FREQ=MONTHLY; BYMONTHDAY=-2;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
sysdate,
add_months(trunc(sysdate,'yyyy'),12)-1));
“1am, 3am, 7am at 1pm every day from tomorrow for a very long time”
select *
from
table(
list_of_dates(
'FREQ=DAILY; BYHOUR=1,4,7,13;BYMINUTE=0;BYSECOND=0',
trunc(sysdate),
date '9999-12-31'));
“First day of each calendar quarter etc”
select *
from
table(
list_of_dates(
'FREQ=YEARLY;BYDATE=0101,0401,0701,1001;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
trunc(sysdate),
date '9999-12-31'));
Guess what this one does:
select *
from
table(
list_of_dates(
'FREQ=DAILY;BYDAY=MON,WED,FRI;BYHOUR=12;BYMINUTE=0;BYSECOND=30',
trunc(sysdate),
date '9999-12-31'));
A couple of other issues worth mentioning:
- The optimiser will think that the function will return 8,168 rows. Possibly there is a way around this.
- You can reference existing schedules as part of the calendaring syntax.
- If you really do stick to using the TIMESTAMP WITH TIME ZONE data type then you can be daylight savings time sensitive (see docs).
Anyway, there you go. I quite like this method because it’s very easy to tackle date lists of just about any complexity with it, especially when you can just copy-paste an existing method and modify it. If you are used to using it then as soon as you see this code you immediately recognise that a list of dates is being generated, and the definition of the list probably leaps out at you in the way that my last example hopefully did.
Comments always welcome, of course.
;BYHOUR=0;BYMINUTE=0;BYSECOND=0
