Scheduler Jobs: Using Time Zone versus GMT Offset
This week after daylight savings time ended here in the Eastern Standard Time Zone, some scheduler jobs were starting one hour early. I have found out that when a job is created with a repeating schedule, and it uses a GMT offset such as GMT -4 for its START_DATE, it will NOT follow daylight savings time. If you create a job using a specific Time Zone, such as US/Eastern, it will follow the daylight savings time schedule. To fix this, you can modify the START_DATE attribute of the scheduler job to use a time zone.
BEGIN
sys.dbms_scheduler.disable( ‘”OWNER”.”JOB_NAME”‘ );
sys.dbms_scheduler.set_attribute( name => ‘”OWNER”.”JOB_NAME”‘, attribute => ‘start_date’, value => to_timestamp_tz(’2009-10-03 US/Eastern’, ‘YYYY-MM-DD TZR’));
sys.dbms_scheduler.enable( ‘”OWNER”.”JOB_NAME”‘ );
END;
/
Note the code will not work while the job is running. When the interval is applied for future job runs, they will run at the proper time.
When you create scheduler jobs using sys.dbms_scheduler.create_job(), pass in the parameter of start_date using a specific timezone, such as “start_date => to_timestamp_tz('2009-11-03 00:00:00 US/Eastern', 'YYYY-MM-DD HH24:MI:SS TZR')” or “start_date => systimestamp at time zone 'US/Eastern'“, unless of course you desire your jobs to follow a specific GMT offset, then use the alternate syntax such as “start_date => to_timestamp_tz('2009-11-02 16:40:00 -5:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')” or “start_date => systimestamp at time zone '-5:00'“.
