1 Reply Latest reply: May 3, 2013 1:37 AM by spajdy RSS

    Setting a Job's starttime to a constant time independent of time transition

    986607
      Hi,

      Oracle version: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production+

      i want put a daily job (DBMS_SCHEDULER) with start_time = 1.30 a.m. and i want this job starts always at 1.30 a.m., indifferent of Summer/Winter Time. How?

      If i put this job as given:

      DBMS_SCHEDULER.CREATE_JOB(
      job_name          => 'MLR.JOB_MLR_REFRESH_MVIEWS',
      job_type          =>  'PLSQL_BLOCK',
      job_action        =>  '....',
      start_date        =>  to_timestamp('2013-05-02:01:30:00','YYYY-MM-DD:HH24:MI:SS'),
      repeat_interval   => 'FREQ=DAILY;INTERVAL=1;BYHOUR=1;BYMINUTE=30;BYSECOND=0',
      enabled  => TRUE,
      auto_drop  => FALSE);

      then i see

      start_date = '02.05.2013 01:30:00,000000 +02:00'

      This job will start at 1.30 a.m. in summer, and at 0.30 a.m. in winter. That is unwanted for me.

      Edited by: user8886932 on 02.05.2013 06:26
        • 1. Re: Setting a Job's starttime to a constant time independent of time transition
          spajdy
          Use timestamp with time zone
          DBMS_SCHEDULER.CREATE_JOB(
          job_name => 'MLR.JOB_MLR_REFRESH_MVIEWS',
          job_type => 'PLSQL_BLOCK',
          job_action => '....',
          start_date => to_timestamp_tz('2013-05-02:01:30:00  Europe/London','YYYY-MM-DD:HH24:MI:SS tzr'),
          repeat_interval => 'FREQ=DAILY;INTERVAL=1;BYHOUR=1;BYMINUTE=30;BYSECOND=0',
          enabled => TRUE,
          auto_drop => FALSE);
          Europe/London is used as example. Use correct value for your time zone