DBMS_SCHEDULER and Winter/Summer time

Recently we have created a scheduled job via the Oracle dbms_scheduler package that resembles to:

BEGIN
  dbms_scheduler.create_job (
    job_name        => 'TEST_TIME_ZONE_JOB',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => systimestamp ,
    repeat_interval => 'freq=daily; byhour=6; byminute=0; bysecond=0;' ,
    end_date        => NULL,
    enabled         => true,
    comments        => 'Job defined to test the effect of time zone in the job start.'
   );
END;
/

This job has been created to start every day at 06H00. Let’s then check what will be the next start date of this job

SQL> select
  2         job_name
  3        ,start_date
  4        ,next_run_date
  5  from
  6        user_scheduler_jobs
  7  where
  8        job_name ='TEST_TIME_ZONE_JOB';

JOB_NAME           START_DATE	     	       NEXT_RUN_DATE
------------------ ----------------------------------- ------------------------------------
TEST_TIME_ZONE_JOB 26-APR-12 11.44.55.610357 AM +02:00  27-APR-12 06.00.00.600000 AM +02:00

What? It will start at 6 AM + 02:00 ??? That’s not what I wanted.

Do you know from where this discrepancy comes? It comes from the job which has been scheduled without taking into account the local time zone. In order to solve this problem, you can re-create your job appropriately as follows

SQL> begin
  2    dbms_scheduler.drop_job('TEST_TIME_ZONE_JOB');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    dbms_scheduler.create_job (
  3      job_name        => TEST_TIME_ZONE_JOB',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN NULL; END;',
  6      start_date      => systimestamp at time zone 'EUROPE/PARIS', --> here is the difference
  7      repeat_interval => 'freq=daily; byhour=6; byminute=0; bysecond=0;' ,
  8      end_date        => NULL,
  9      enabled         => true,
 10      comments        => 'Job defined to test the effect of time zone in the job start.'
 11     );
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> select
  2         job_name
  3        ,start_date
  4        ,next_run_date
  5  from
  6        user_scheduler_jobs
  7  where
  8        job_name ='TEST_TIME_ZONE_JOB';

JOB_NAME           START_DATE                                NEXT_RUN_DATE
------------------ ----------------------------------------  -----------------------------------------
TEST_TIME_ZONE_JOB 26-APR-12 11.46.02.075991 AM EUROPE/PARIS 27-APR-12 06.00.00.100000 AM EUROPE/PARIS

As such, the next run date is scheduled exactly as the desired time (06H00).
The DBA can also set the dbms_scheduler parameter so that the appropriate time zone (‘EUROPE/PARIS’, in my case) could be set as a default value:

begin
   dbms_scheduler.set_scheduler_attribute('DEFAULT_TIMEZONE', 'EUROPE/PARIS’);
end;

I hope this simple article will help avoiding such a kind of automatic change in the start time of a scheduled job when there is a shift between winter/summer time.