msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

The Oracle Scheduler, Daylight Saving Time and Timezones

The Oracle Scheduler is a very powerful and flexible scheduling engine that is part of the Oracle database. In this article, we will observe how the Scheduler behaves when daylight saving time (DST) begins and ends in time zones that use DST.

The datetime value is sufficient for Oracle to determine whether daylight saving time is in effect for a given region in all cases except boundary cases. A boundary case occurs during the period when daylight saving goes into or comes out of effect. For example, in the US/Pacific region, when daylight saving goes into effect (as of this writing the next one is on Sunday March 13, 2011 at 2:00 a.m.), the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight saving goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 a.m. and 2 a.m. is repeated (source).

The question is, what happens to your repeating Oracle Scheduler jobs during these boundary periods?

Let’s say you have a job you want to schedule every 6 hours starting at 5 a.m. You want the job to run every day at 5 a.m., 11 a.m., 5 p.m. and 11 p.m. (or 5, 11, 17 and 23 in HH24). The calendar expression for this schedule (the value of the repeat_interval in DBMS_SCHEDULER.create_job for example) could be defined in two ways:

  1. ‘FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0′
  2. ‘FREQ=HOURLY; INTERVAL=6′

What I am going to show you next are important differences between the two calendar expressions, specifically relating to the boundary cases and timezone definition.

For the test below, I am going to use a very handy procedure: DBMS_SCHEDULER.evaluate_calendar_string. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job.

We’ll start by setting the value of nls_timestamp_tz_format. This parameter defines the default timestamp with time zone format to use with the TO_TIMESTAMP_TZ functions. I made sure that both TZR and TZD are part of the format.

TZR represents the time zone region name. Examples are ‘Australia/North’, ‘UTC’, and ‘Singapore’. TZD represents an abbreviated form of the time zone region name with daylight saving information. Examples are ‘PST’ for US/Pacific standard time and ‘PDT’ for US/Pacific daylight time. To see a listing of valid values for the TZR and TZD format elements, query the TZNAME and TZABBREV columns of the V$TIMEZONE_NAMES dynamic performance view (source).

    eddie@DB112> SET SERVEROUTPUT ON;
    eddie@DB112> ALTER SESSION
      2  SET nls_timestamp_tz_format = 'MM-DD-YYYY HH24:MI:SS tzr tzd';

    Session altered.

Next I declare an inline procedure that is basically a wrapper around evaluate_calendar_string , just to save typing and repetition as I will be calling it multiple times:

eddie@DB112> DECLARE
  2     PROCEDURE list_next_run_dates (
  3        start_date_i        IN TIMESTAMP WITH TIME ZONE,
  4        calendar_string_i   IN VARCHAR2,
  5        date_count_i        IN INTEGER DEFAULT 8 )
  6     IS
  7        l_return_date_after   TIMESTAMP WITH TIME ZONE;
  8        l_next_run_date       TIMESTAMP WITH TIME ZONE;
  9     BEGIN
 10        l_return_date_after := start_date_i;
 11
 12        FOR i IN 1 .. date_count_i
 13        LOOP
 14           DBMS_SCHEDULER.evaluate_calendar_string (
 15              calendar_string     => calendar_string_i,
 16              start_date          => start_date_i,
 17              return_date_after   => l_return_date_after,
 18              next_run_date       => l_next_run_date);
 19
 20           DBMS_OUTPUT.put_line (l_next_run_date);
 21
 22           l_return_date_after := l_next_run_date;
 23        END LOOP;
 24     END list_next_run_dates;

Then I call list_next_run_dates multiple times, each time with a different start date and calendar string. In all cases, the month, day and year portion of the start date is the same, March 11, 2011. I made sure that the run dates span the period when daylight saving goes into effect (March 13, 2011 at 2:00 a.m.):

 25  BEGIN
 26   DBMS_OUTPUT.put_line ('1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY');
 27   list_next_run_dates (
 28      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 29      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 30
 31   DBMS_OUTPUT.put_line ('2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY');
 32   list_next_run_dates (
 33      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 34      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 35
 36   DBMS_OUTPUT.put_line ('3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY');
 37   list_next_run_dates (
 38      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 39      'FREQ=DAILY; BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0');
 40
 41   DBMS_OUTPUT.put_line ('4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY');
 42   list_next_run_dates (
 43      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 US/Pacific'),
 44      'FREQ=HOURLY; INTERVAL=6');
 45
 46   DBMS_OUTPUT.put_line('5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY');
 47   list_next_run_dates (
 48      TO_TIMESTAMP_TZ ('03-11-2011 23:10:00 US/Pacific'),
 49      'FREQ=HOURLY; INTERVAL=6');
 50
 51   DBMS_OUTPUT.put_line ('6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY');
 52   list_next_run_dates (
 53      TO_TIMESTAMP_TZ ('03-11-2011 23:00:00 -08:00'),
 54      'FREQ=HOURLY; INTERVAL=6');
 55  END;
 56  /

Let’s examine the results:

Specifying FREQ=DAILY does not account for DST change. We lose one hour between these two runs as there are only five hours between “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 05:00:00 US/Pacific PDT”. Notice that PST becomes PDT when DST starts:

1: 03-11-2011 23:00:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

The second test is the same as the first except that I add 10 minutes to the start date. The result stays the same. The start date provides the month, day and year after which the job can run. It also provides the timezone. However, the hour, minute and seconds are read from the calendar string, “BYHOUR=5,11,17,23; BYMINUTE=0; BYSECOND=0″:

2: 03-11-2011 23:10:00 US/Pacific : FREQ=DAILY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 05:00:00 US/Pacific PDT
03-13-2011 11:00:00 US/Pacific PDT
03-13-2011 17:00:00 US/Pacific PDT
03-13-2011 23:00:00 US/Pacific PDT

Again, there is no change in the result if we use a timezone offset (-08:00) instead of a timezone region (US/Pacific). Because of the fact that we are using FREQ=DAILY, the DST is not taken into account. Moreover, a timezone offset is not DST aware.

3: 03-11-2011 23:00:00 -08:00 : FREQ=DAILY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Now, let’s switch to FREQ=HOURLY. Here, we are using a timezone region which means that we care about DST. The calendar expression in this case is an interval of 6 hours. This interval is preserved when DST changes. There are 6 hours between these two dates: “03-12-2011 23:00:00 US/Pacific PST” and “03-13-2011 06:00:00 US/Pacific PDT”. However, notice that the scheduled hours have changed from 5,11,17,23 to 6,12,18,00:

4:03-11-2011 23:00:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:00:00 US/Pacific PST
03-12-2011 11:00:00 US/Pacific PST
03-12-2011 17:00:00 US/Pacific PST
03-12-2011 23:00:00 US/Pacific PST
03-13-2011 06:00:00 US/Pacific PDT
03-13-2011 12:00:00 US/Pacific PDT
03-13-2011 18:00:00 US/Pacific PDT
03-14-2011 00:00:00 US/Pacific PDT

The calendar expression ‘FREQ=HOURLY; INTERVAL=6′ does not contain hours, minutes and seconds definitions. In this case, where the hours, minutes and seconds are not explicitly defined in the calendar string, their values are defaulted from the start date. That’s why we see 10 minutes added to all the scheduled runs:

5: 03-11-2011 23:10:00 US/Pacific : FREQ=HOURLY
03-12-2011 05:10:00 US/Pacific PST
03-12-2011 11:10:00 US/Pacific PST
03-12-2011 17:10:00 US/Pacific PST
03-12-2011 23:10:00 US/Pacific PST
03-13-2011 06:10:00 US/Pacific PDT
03-13-2011 12:10:00 US/Pacific PDT
03-13-2011 18:10:00 US/Pacific PDT
03-14-2011 00:10:00 US/Pacific PDT

If we use a timezone offset instead of a timezone region, DST is not honored:

6: 03-11-2011 23:00:00 -08:00 : FREQ=HOURLY
03-12-2011 05:00:00 -08:00
03-12-2011 11:00:00 -08:00
03-12-2011 17:00:00 -08:00
03-12-2011 23:00:00 -08:00
03-13-2011 05:00:00 -08:00
03-13-2011 11:00:00 -08:00
03-13-2011 17:00:00 -08:00
03-13-2011 23:00:00 -08:00

Lessons Learned:

  • When checking the various DBMS_SCHEDULER time fields make sure you are using a NLS_TIMESTAMP_TZ_FORMAT that display’s the timezone information, more specifically the “TZR TZD” mask so that you can quickly see in what format the data is stored.
  • Timezone offsets are by definition not DST aware. So any job using an offset as timezone will run at a wrong time after a DST change.
  • To have a job that automatically adjusts for DST you need to make sure it’s defined with a timezone region name that actually uses DST rules.
  • If you want your daily repeating job to run at specific hours every day and you do not want these hours to change when DST changes, make sure you have a calendar expression that uses FREQ=DAILY and define the hours in “BYHOUR=…”.
  • If you want your daily repeating job to run at a specific hourly interval and you do not mind the hour of day change when DST changes, make sure you have a calendar expression that uses FREQ=HOURLY and define the interval in “INTERVAL=…”.

Sources and resources:


Filed in Oracle, Tips on 07 Dec 10 | Tags:


Reader's Comments

  1. |

    Very interesting. I was wondering about this, but it wasn’t a problem I had to solve because our systems are all GMT, with none of this daylight savings time nonsense.

    btw I recently wrapped dbms_scheduler.evaluate_calendar_string in a pipelined function to give a nice clean output for getting future execution timestamps. Works quite nicely: http://oraclesponge.wordpress.com/2010/08/18/generating-lists-of-dates-in-oracle-the-dbms_scheduler-way/

    • |

      Very neat and creative use of the procedure evaluate_calendar_string which makes even more handy. You’re lucky you don’t have to deal with DST.

  2. |

    Thanks for this. I hope I never have to use it though. I worked on one system that had to account for daylight saving and it was a lot of trouble for those two days of the year.

    Never understood what is accomplished by daylight saving that couldn’t be done better by people just setting their alarm clocks to go off an hour earlier or later in the mornings.

    • |

      These daylight saving border cases feel like a Y2K that happens twice a year.

      • |

        … at different times in, and within, different countries, with the underlying logic changing according to the political temperament. So rather worse in some ways.

  3. |

    […] expression and tells you what the next execution date and time of a job will be. Consult my previous post or this forum post for examples of how to use […]