Generating Lists of Dates in Oracle the DBMS_Scheduler Way

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:

  1. Generate a rather vanilla list of dates using a CONNECT BY clause on the DUAL table
  2. 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::

  1. 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.
  2. 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′
  3. 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:

  1. The optimiser will think that the function will return 8,168 rows. Possibly there is a way around this.
  2. You can reference existing schedules as part of the calendaring syntax.
  3. 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
About these ads

12 thoughts on “Generating Lists of Dates in Oracle the DBMS_Scheduler Way

    • Ah yes, plenty of choices there then.

      I expect that whatever the complexity of the calendar people generally do have a reasonable idea of the cardinality that they’re expecting, so the extensible optimiser would be an interesting choice. For example, if one was generating a list of all the Mondays and Thursdays in a single year then 52*2 would be a close estimate. Given particular start and end dates you could just estimate the cardinality for that calendar as “days between start and end dates multiplied by 2/7″. Without the fractions, of course.

      Thanks for the input.

  1. Nice. Very nice.
    What’s not to like?
    A little bit different? Check.
    A little bit clever? Check.
    Reuse of tried and tested functionality? Check.

    • Cheers Dom — it’s not to everyone’s taste I’m sure, and the instinctive reaction is probably “Neat trick but it’s not for me at the moment”.

      The truth is that most people are going to get by with a simple pure SQL technique for generating lists of dates. However, if you have struggled to define a more complex list using SQL and have then implemented this solution, I think there would be a temptation to go back and refactor previous lists to use the same technique. It’s definitely one of those issues where it looks like too much effort for the first simple implementation, but once in use it’s very simple to re-use.

      I really ought to add in the cardinality estimation based on the Extensible Optimizer technique.

  2. Pingback: Listing DBMS_Scheduler Named-Calendar Dates with SQL « The Oracle Sponge

  3. One sticking point on my system is when to ship products to customers. Several algorithms have been used over time (“if it’s before noon on Thursday…”), eventually to be replaced by “make the order entry people put in a date.” Now with EDI, the customer is saying what the date is they expect it at their door, so without order entry people input… At one point I unthinkingly suggested using a calendar, so we could account for holidays and such, and of course that brings out the fact that customers have different locations with different holidays, some of which may or may not be celebrated, and our sites have the same… makes me want to roll a Mayan calendar at someone.

    • Hmmm, I was accidentally thinking about this while gardening this weekend. My first thought had been that a requirement to find a date prior to a particular date, as your requirement seems to be, exposes a weakness of this dbms_scheduler method because it only does “ascending” calendar calculations. It’s inherent to the way that the DBMS_Scheduler subprogram works that it finds the next date, not the previous date. But in fact you could restate your requirement as needing to find the date for which the next date in the sequence is the required delivery date.

      It’s interesting to wonder how most efficiently to cope with different customers having different calendars of work and non-work days. One method would be to define general calendars to be referenced by all customers, and then maintain lists of included/excluded days on a per customer/customer-location basis that can be string-aggregated into the scheduler calendaring syntax for use in INCLUDE and EXCLUDE clauses. sounds fraught with problems though.

      How did you resolve the issue in the end Joel?

      • Not resolved, waiting on business requirements to be clarified. Since EDI tends to have a lot of per-customer customization, I expect a non-global solution, and probably several go-arounds as people try to force a global solution. The people who actually put stuff on the truck are the ones who know what the shipping conditions are (like, Colorado Springs in February), but the information is putatively needed at order time, which could be months earlier, or could be JIT. Good times.

        My friends once had a band called “Big Food.” They had a song circa 1984 called Gardening, which IIRC started with the lines:
        I really like gardening
        I really like to stand in sh*t

        Oddly apropros, eh?

  4. Pingback: 25+ Unique Ways to Schedule a Job Using the Oracle Scheduler » Eddie Awad's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s