As we approach the end of Daylight Saving Time in the United States for 2019, I thought it would be a good time to double check on some of the job schedules defined in the database.
If we have a job scheduled to run at 4pm every day, we don’t want it to become a 5pm Eastern Standard Time job in November because the schedule is still running in Eastern Daylight Time.
Using a fixed offset time zone produces a schedule with a fixed offset. Note the highlighted parameter value with time zone “-04:00”.
SQL> BEGIN
2 DBMS_SCHEDULER.drop_job(job_name => 'SAMPLE_DST_JOB');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.create_job(
3 job_name => 'SAMPLE_DST_JOB',
4 start_date => TO_TIMESTAMP_TZ('2019-10-11 18:42:54 -04:00', 'yyyy-mm-dd hh24:mi:ss TZH:TZM'),
5 repeat_interval => 'FREQ=daily',
6 job_type => 'PLSQL_BLOCK',
7 job_action => 'begin null; end;');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SELECT TO_CHAR(start_date, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr') start_date,
2 repeat_interval
3 FROM user_scheduler_jobs
4 WHERE job_name = 'SAMPLE_DST_JOB';
START_DATE REPEAT_INTERVAL
------------------------------------ --------------------
2019-10-11 18:42:54 -04:00 , -04:00 FREQ=daily
SQL> DECLARE
2 v_calendar user_scheduler_jobs.repeat_interval%TYPE;
3 v_start user_scheduler_jobs.start_date%TYPE;
4 v_after_this TIMESTAMP WITH TIME ZONE;
5 v_next TIMESTAMP WITH TIME ZONE;
6 BEGIN
7 SELECT start_date, repeat_interval
8 INTO v_start, v_calendar
9 FROM user_scheduler_jobs
10 WHERE job_name = 'SAMPLE_DST_JOB';
11
12 DBMS_OUTPUT.put_line(TO_CHAR(v_start, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr'));
13
14 v_after_this := v_start;
15
16 --- Generate 30 days of start times
17 FOR i IN 1 .. 30
18 LOOP
19 DBMS_SCHEDULER.evaluate_calendar_string(calendar_string => v_calendar,
20 start_date => v_start,
21 return_date_after => v_after_this,
22 next_run_date => v_next);
23
24 DBMS_OUTPUT.put_line(TO_CHAR(v_next, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr'));
25 v_after_this := v_next;
26 END LOOP;
27 END;
28 /
2019-10-11 18:42:54 -04:00 , -04:00
2019-10-12 18:42:54 -04:00 , -04:00
2019-10-13 18:42:54 -04:00 , -04:00
2019-10-14 18:42:54 -04:00 , -04:00
2019-10-15 18:42:54 -04:00 , -04:00
2019-10-16 18:42:54 -04:00 , -04:00
2019-10-17 18:42:54 -04:00 , -04:00
2019-10-18 18:42:54 -04:00 , -04:00
2019-10-19 18:42:54 -04:00 , -04:00
2019-10-20 18:42:54 -04:00 , -04:00
2019-10-21 18:42:54 -04:00 , -04:00
2019-10-22 18:42:54 -04:00 , -04:00
2019-10-23 18:42:54 -04:00 , -04:00
2019-10-24 18:42:54 -04:00 , -04:00
2019-10-25 18:42:54 -04:00 , -04:00
2019-10-26 18:42:54 -04:00 , -04:00
2019-10-27 18:42:54 -04:00 , -04:00
2019-10-28 18:42:54 -04:00 , -04:00
2019-10-29 18:42:54 -04:00 , -04:00
2019-10-30 18:42:54 -04:00 , -04:00
2019-10-31 18:42:54 -04:00 , -04:00
2019-11-01 18:42:54 -04:00 , -04:00
2019-11-02 18:42:54 -04:00 , -04:00
2019-11-03 18:42:54 -04:00 , -04:00
2019-11-04 18:42:54 -04:00 , -04:00
2019-11-05 18:42:54 -04:00 , -04:00
2019-11-06 18:42:54 -04:00 , -04:00
2019-11-07 18:42:54 -04:00 , -04:00
2019-11-08 18:42:54 -04:00 , -04:00
2019-11-09 18:42:54 -04:00 , -04:00
2019-11-10 18:42:54 -04:00 , -04:00
PL/SQL procedure successfully completed.
Note the time offset remains -04:00 from GMT/UTC even after the DST transition.
Using a named time zone that observes DST transistions will produce a schedule with a variable offset in the time zone, observing DST changes. Here the highlighted start_date value uses “US/Eastern”.
SQL> BEGIN
2 DBMS_SCHEDULER.drop_job(job_name => 'SAMPLE_DST_JOB');
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_SCHEDULER.create_job(
3 job_name => 'SAMPLE_DST_JOB',
4 start_date => TO_TIMESTAMP_TZ('2019-10-11 18:42:54 US/Eastern', 'yyyy-mm-dd hh24:mi:ss TZR'),
5 repeat_interval => 'FREQ=daily',
6 job_type => 'PLSQL_BLOCK',
7 job_action => 'begin null; end;');
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> SELECT TO_CHAR(start_date, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr') start_date,
2 repeat_interval
3 FROM user_scheduler_jobs
4 WHERE job_name = 'SAMPLE_DST_JOB';
START_DATE REPEAT_INTERVAL
---------------------------------------- --------------------
2019-10-11 18:42:54 -04:00 , US/Eastern FREQ=daily
SQL> DECLARE
2 v_calendar user_scheduler_jobs.repeat_interval%TYPE;
3 v_start user_scheduler_jobs.start_date%TYPE;
4 v_after_this TIMESTAMP WITH TIME ZONE;
5 v_next TIMESTAMP WITH TIME ZONE;
6 BEGIN
7 SELECT start_date, repeat_interval
8 INTO v_start, v_calendar
9 FROM user_scheduler_jobs
10 WHERE job_name = 'SAMPLE_DST_JOB';
11
12 DBMS_OUTPUT.put_line(TO_CHAR(v_start, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr'));
13
14 v_after_this := v_start;
15
16 --- Generate 30 days of start times
17 FOR i IN 1 .. 30
18 LOOP
19 DBMS_SCHEDULER.evaluate_calendar_string(calendar_string => v_calendar,
20 start_date => v_start,
21 return_date_after => v_after_this,
22 next_run_date => v_next);
23
24 DBMS_OUTPUT.put_line(TO_CHAR(v_next, 'yyyy-mm-dd hh24:mi:ss tzh:tzm , tzr'));
25 v_after_this := v_next;
26 END LOOP;
27 END;
28 /
2019-10-11 18:42:54 -04:00 , US/Eastern
2019-10-12 18:42:54 -04:00 , US/Eastern
2019-10-13 18:42:54 -04:00 , US/Eastern
2019-10-14 18:42:54 -04:00 , US/Eastern
2019-10-15 18:42:54 -04:00 , US/Eastern
2019-10-16 18:42:54 -04:00 , US/Eastern
2019-10-17 18:42:54 -04:00 , US/Eastern
2019-10-18 18:42:54 -04:00 , US/Eastern
2019-10-19 18:42:54 -04:00 , US/Eastern
2019-10-20 18:42:54 -04:00 , US/Eastern
2019-10-21 18:42:54 -04:00 , US/Eastern
2019-10-22 18:42:54 -04:00 , US/Eastern
2019-10-23 18:42:54 -04:00 , US/Eastern
2019-10-24 18:42:54 -04:00 , US/Eastern
2019-10-25 18:42:54 -04:00 , US/Eastern
2019-10-26 18:42:54 -04:00 , US/Eastern
2019-10-27 18:42:54 -04:00 , US/Eastern
2019-10-28 18:42:54 -04:00 , US/Eastern
2019-10-29 18:42:54 -04:00 , US/Eastern
2019-10-30 18:42:54 -04:00 , US/Eastern
2019-10-31 18:42:54 -04:00 , US/Eastern
2019-11-01 18:42:54 -04:00 , US/Eastern
2019-11-02 18:42:54 -04:00 , US/Eastern
2019-11-03 18:42:54 -05:00 , US/Eastern
2019-11-04 18:42:54 -05:00 , US/Eastern
2019-11-05 18:42:54 -05:00 , US/Eastern
2019-11-06 18:42:54 -05:00 , US/Eastern
2019-11-07 18:42:54 -05:00 , US/Eastern
2019-11-08 18:42:54 -05:00 , US/Eastern
2019-11-09 18:42:54 -05:00 , US/Eastern
2019-11-10 18:42:54 -05:00 , US/Eastern
PL/SQL procedure successfully completed.
Here the schedule changed from -04:00 to -05:00 to go along with the transition from Daylight to Standard time.
There may be occasions to maintain a consistent schedule, and of course, if your job runs in an area that does not observe DST changes then you won’t want a changing schedule. For those that do though, it’s important to note the difference in schedule definitions.
In the examples above the START_DATE was defined within the job itself. The same time zone and daylight saving time observation applies if using a named schedule with the START_DATE defined there.
If your job already exists, you don’t need to drop and recreate it to change the time zone. You can set that attribute directly. Here I use FROM_TZ to help highlight the intention of setting the time zone.
BEGIN
DBMS_SCHEDULER.set_attribute(
name => 'SAMPLE_DST_JOB',
attribute => 'START_DATE',
VALUE => FROM_TZ(TIMESTAMP '2019-10-11 18:42:54', 'US/Eastern')
);
END;
Steven Green pointed out “at time zone” syntax I left out of my examples.
That is the “at time zone” modifier for timestamp with time zone values
for example:
systimestamp at time zone ‘US/Eastern’