Hi all,
Some time ago a client asked me to check why a Job started running in a different time. This was a week after the DST change. Easy guess, right?
The job was scheduled using timezone offset (PM-04:00) instead of timezone region. Considering the DST change, the offset changed as well (-5:00), but not the job schedule.
To fix the job time definitely, I changed schedule to use timezone region that database is in. Check below the investigation and solution:
SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';
OWNER JOB_NAME REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE
---------- ---------- ----------------- ----------------------------------- --------------------
GREPORA MYJOB FREQ=DAILY; 08-NOV-17 09.00.00.330739 PM -04:00 08-NOV-17 09.00.00.300000 PM -04:00
SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'DEFAULT_TIMEZONE';
ATTRIBUTE_NAME VALUE
----------------- ---
DEFAULT_TIMEZONE EST5EDT
SQL> SELECT tzabbrev, TZ_OFFSET(tzname), tzname FROM V$TIMEZONE_NAMES
WHERE tzname IN ('EST', 'EDT') OR tzabbrev IN ('EST', 'EDT') ORDER BY 1,2;
TZABBREV TZ_OFFS TZNAME
--------- ------- ---------------------------
EDT -04:00 America/Santo_Domingo
EDT -05:00 America/Fort_Wayne
EDT -05:00 America/Grand_Turk
EDT -05:00 America/Indiana/Indianapolis
EDT -05:00 America/Indiana/Marengo
EDT -05:00 America/Indiana/Petersburg
EDT -05:00 US/Michigan
EDT -05:00 America/Detroit
EDT -05:00 US/Eastern
EDT -05:00 US/East-Indiana
EDT -05:00 Jamaica
EDT -05:00 EST5EDT
EDT -05:00 Canada/Eastern
EDT -05:00 America/Toronto
EDT -05:00 America/Thunder_Bay
EDT -05:00 America/Port-au-Prince
EDT -05:00 America/Pangnirtung
EDT -05:00 America/Nipigon
EDT -05:00 America/New_York
EDT -05:00 America/Nassau
EDT -05:00 America/Montreal
EDT -05:00 America/Louisville
EDT -05:00 America/Kentucky/Monticello
EDT -05:00 America/Kentucky/Louisville
EDT -05:00 America/Jamaica
EDT -05:00 America/Iqaluit
EDT -05:00 America/Indiana/Vevay
EDT -05:00 America/Indiana/Vincennes
EDT -05:00 America/Indiana/Winamac
EDT -05:00 America/Indianapolis
EDT -06:00 America/Indiana/Tell_City
EDT -06:00 America/Cancun
EST +10:00 Australia/Queensland
EST +10:00 Australia/Lindeman
EST +10:00 Australia/Brisbane
EST +10:30 Australia/Broken_Hill
EST +10:30 Australia/Yancowinna
EST +11:00 Australia/Currie
EST +11:00 Australia/Canberra
EST +11:00 Australia/ACT
EST +11:00 Antarctica/Macquarie
EST +11:00 Australia/Hobart
EST +11:00 Australia/LHI
EST +11:00 Australia/Lord_Howe
EST +11:00 Australia/Melbourne
EST +11:00 Australia/NSW
EST +11:00 Australia/Sydney
EST +11:00 Australia/Tasmania
EST +11:00 Australia/Victoria
EST -04:00 America/Antigua
EST -04:00 America/Moncton
EST -04:00 America/Santo_Domingo
EST -05:00 US/Eastern
EST -05:00 America/Grand_Turk
EST -05:00 America/Atikokan
EST -05:00 Jamaica
EST -05:00 EST5EDT
EST -05:00 EST
EST -05:00 America/Indiana/Indianapolis
EST -05:00 Canada/Eastern
EST -05:00 America/Toronto
EST -05:00 America/Thunder_Bay
EST -05:00 America/Resolute
EST -05:00 America/Indiana/Marengo
EST -05:00 America/Indiana/Petersburg
EST -05:00 America/Cayman
EST -05:00 America/Indiana/Vevay
EST -05:00 America/Indiana/Vincennes
EST -05:00 America/Indiana/Winamac
EST -05:00 America/Indianapolis
EST -05:00 America/Iqaluit
EST -05:00 America/Jamaica
EST -05:00 America/Kentucky/Louisville
EST -05:00 America/Kentucky/Monticello
EST -05:00 US/Michigan
EST -05:00 America/Louisville
EST -05:00 America/Coral_Harbour
EST -05:00 America/Detroit
EST -05:00 America/Fort_Wayne
EST -05:00 America/Montreal
EST -05:00 America/Nassau
EST -05:00 America/New_York
EST -05:00 America/Nipigon
EST -05:00 America/Panama
EST -05:00 America/Pangnirtung
EST -05:00 America/Port-au-Prince
EST -05:00 US/East-Indiana
EST -06:00 US/Central
EST -06:00 CST
EST -06:00 America/Rankin_Inlet
EST -06:00 America/Merida
EST -06:00 America/Menominee
EST -06:00 America/Managua
EST -06:00 America/Knox_IN
EST -06:00 America/Indiana/Tell_City
EST -06:00 America/Indiana/Knox
EST -06:00 America/Chicago
EST -06:00 America/Cancun
EST -06:00 US/Indiana-Starke
EST -07:00 America/Cambridge_Bay
100 rows selected.
SQL> BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'GREPORA.MYJOB',
attribute => 'start_date',
value => TO_TIMESTAMP_TZ('09-11-2017 21:00:00 EST5EDT' ,'DD-MM-YYYY HH24:MI:SS TZR'));
END;
/
PL/SQL procedure successfully completed.
SQL> select owner,job_name, repeat_interval, last_start_date, next_run_date from dba_scheduler_jobs where job_name='MYJOB';
OWNER JOB_NAME REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE
---------- ---------- ----------------- ------------------------------- --------------------
GREPORA MYJOB FREQ=DAILY; 08-NOV-17 09.00.00.3 09-NOV-17 09-NOV-17 09.00.00.0
Hope this helps you!
Cheers!
