This discussion is archived
10 Replies Latest reply: Nov 13, 2012 7:32 AM by EdStevens RSS

DST questions, part duex

EdStevens Guru
Currently Being Moderated
Oracle 11.20.1.0 SE-One
Oracle Linux 5.6 x86-64

Cut to the chase. Most of our scheduler jobs are defined such that start_date, last_start_date, and next_run_date all specify the timezone with a fixed offset. As a result, the first execution after the DST time change, they ran one hour early, but then computation for the next run corrected and they fell back to running at the scheduled time.

Well, almost.

We have two application schemas that own scheduler jobs.

The jobs in one schema behaved as above.

But all of the jobs in the other schema continue to run one hour early.

the above can be observed in the output from this query:
select
     instance_name
,     systimestamp
from
     v$instance;
--
select 
     owner
,     job_name
,     log_date
,     status
from
     dba_scheduler_job_log
here   log_date > sysdate - 10
  and   owner not in ('EXFSYS',
                      'ORACLE_OCM',
                      'PERFSTAT',
                      'SYS'
                      )
order by
     owner
,     job_name
,     log_date
;
Very easy to just go down the LOG_DATE column and see the time change.

So my first of two questions is - can anyone explain the above behavior, especially the inconsistency in the jobs of one schema 'healing' themselves after the first run after the time change, but the jobs of another schema continuing to run an hour early.

I don't know if it bears on it or not, but most of the jobs (both schemas) are scheduled with a home-grown function for 'frequency' that uses a decimal representation of time (ie: WEEKLY(.4583,'SATURDAY') )


And a second question - what are the things that influence the timezone representation in the START_DATE when defining a new job? Our programmer creates/maintains the jobs using SQL Navigator, and there is no provision in the GUI there for specifying either a specific time zone or the method (offset vs. name) of representing a timezone. I've read that there are some client-side influences but haven't pieced that together just yet.
  • 1. Re: DST questions, part duex
    spajdy Pro
    Currently Being Moderated
    1/ give to us result of queries because we don't have access to your system and code for functions you are using in repeat_interval

    2/ correct DST behaviour in Scheduler job is guarented only when start_date has specified time zone in format region/city (Europe/Prague). If you specify time zone in absolute offsett +1:00 it doesn't work - see Oracle Doc.

    •The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.

    •When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:
    1.
    It will check whether the session time zone is a region name. The session time zone can be set by either:

    Issuing an ALTER SESSION statement, for example:
    SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';


    Setting the ORA_SDTZ environment variable.


    2.
    If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

    3.
    If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.


    3/ timezone for start date is derived from session you are createding a job. I suggest use function to_timestamp_tz to define requested timezone
  • 2. Re: DST questions, part duex
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    1/ give to us result of queries because we don't have access to your system and code for functions you are using in repeat_interval
    After more reading and some additional testing, I really don't think these home-grown functions are germain to the issue. They are used throughout the application and so are not a distinctive factor between those jobs that 'healed' themselves vs. those that haven't.

    2/ correct DST behaviour in Scheduler job is guarented only when start_date has specified time zone in format region/city (Europe/Prague). If you specify time zone in absolute offsett +1:00 it doesn't work - see Oracle Doc.

    •The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.

    •When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:
    1.
    It will check whether the session time zone is a region name. The session time zone can be set by either:

    Issuing an ALTER SESSION statement, for example:
    SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';


    Setting the ORA_SDTZ environment variable.


    2.
    If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

    3.
    If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.

    Yes, I already knew the above. I guess I didn't make it clear from the beginning that I was already past that hurdle.
    3/ timezone for start date is derived from session you are createding a job. I suggest use function to_timestamp_tz to define requested timezone
    And this is where I'm still scratching -- to find all the influences there. Though in fairness to you and the rest of the forum, since my last post I may have isolated this to an issue with the SQL Navigator tool my developers use to manage job scheduling. I was doing some more testing this morning, specifically with the tool, and there doesn't appear to be a mechanism to control that within the tool. I've opened a thread on their support forum to follow up.

    Which really just leaves one question for this forum ...
    Given a collection of jobs, all of which
    - use the same home-grown functions to provide values for repeat_interval,
    - have a time offset (as opposed to a named TZ) in their START_DATE

    We saw two distinct sets of behavior:
    1) First run of a job after the time change occurred one hour early. Subsequent runs are back on schedule.
    2) All executions of jobs after time change occur one hour early; no re-adjustment back to specified times.

    The only difference we see between the two sets of behavior is that they coincide with which of two schemas owns the jobs.
  • 3. Re: DST questions, part duex
    spajdy Pro
    Currently Being Moderated
    Good, but without any output from you system we could only guess.
    So could you give us code of your functions used in repeat_interval and result of select * from dba_scheduler_jobs where job_name in (...).

    You wrote:
    The only difference we see between the two sets of behavior is that they coincide with which of two schemas owns the jobs.

    My tip - different NLS settings for jobs. Check NLS_ENV column in dba_scheduler_jobs, loggon trigger on schemas owns jobs.
  • 4. Re: DST questions, part duex
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    Good, but without any output from you system we could only guess.
    So could you give us code of your functions used in repeat_interval and result of select * from dba_scheduler_jobs where job_name in (...).

    You wrote:
    The only difference we see between the two sets of behavior is that they coincide with which of two schemas owns the jobs.

    My tip - different NLS settings for jobs. Check NLS_ENV column in dba_scheduler_jobs, loggon trigger on schemas owns jobs.
    Ok, now I've established a pattern that DOES point back to the custom functions. The jobs under the schema that remain an hour early are all using standard oracle calendaring syntax. The jobs that 'corrected' themselves after the first post-time-change run - after running an hour early one time -- are all using the custom functions. There are a few jobs in that schema using standard calendaring syntax, but they have long repeat cycles (minimum is one week, most are monthly or longer) and have not yet had a second run to compare.

    That said, here's an example -- the 'MON_TO_FRI' function
    FUNCTION      "MON_TO_FRI"   ( time_of_day
         in number)
      return date
      is
          l_date date;
      begin
          l_date := trunc(least(next_day(SYSDATE,'MONDAY'),
            next_day(SYSDATE,'TUESDAY'),
            next_day(SYSDATE,'WEDNESDAY'),
            next_day(SYSDATE,'THURSDAY'),
            next_day(SYSDATE,'FRIDAY')))+time_of_day;
    
          return l_date;
      end mon_to_fri;
    And again, a typical usage in REPEAT_INTERVAL would be
    MON_TO_FRI(.271)
  • 5. Re: DST questions, part duex
    spajdy Pro
    Currently Being Moderated
    Half done, you give us code of your function.
    Now give us result of:
    select * from dba_scheduler_jobs where job_name in (<job1>,<job2>);
    select dbms_scheduler.stime, systimestamp from dual;
    replace <job1>,<job2> with real names of you puzzling jobs.
  • 6. Re: DST questions, part duex
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    Half done, you give us code of your function.
    Now give us result of:
    select * from dba_scheduler_jobs where job_name in (<job1>,<job2>);
    select dbms_scheduler.stime, systimestamp from dual;
    replace <job1>,<job2> with real names of you puzzling jobs.
    Here's a sample
    OWNER      JOB_NAME             START_DATE                           NEXT_RUN_DATE                        REPEAT_INTERVAL
    ---------- -------------------- ------------------------------------ ------------------------------------ ---------------------------------------------
    OWNER1     EVENT_TRANSFER       05-NOV-12 10.30.00.000000 AM -05:00  13-NOV-12 10.30.00.000000 AM -06:00  MON_TO_FRI(.4375)
    OWNER2     JOB_PEOPLESOFT_EXTRA 09-NOV-12 09.45.00.000000 AM -06:00  13-NOV-12 09.45.00.000000 AM -06:00  FREQ=DAILY; INTERVAL=1; BYDAY=MON,TUE,WED,THU
    
    SQL> select dbms_scheduler.stime, systimestamp from dual;
    
    STIME                                                                       
    SYSTIMESTAMP
    --------------------------------------------------------------------------- ----
    -----------------------------------------------------------------------
    12-NOV-12 11.45.24.658242000 AM CST6CDT                                     
    12-NOV-12 11.45.24.644058 AM -06:00
    
    SQL>
    The job using the home-grown-function MON_TO_FRI (code shown in earlier post) ran one hour early the first time after the time change, then was back on track.
    The other job, using standard oracle calendar syntax, continues to run one hour early.
  • 7. Re: DST questions, part duex
    spajdy Pro
    Currently Being Moderated
    Job JOB_PEOPLESOFT_EXTRA, repeat_interval is in CALENDAR, syntax start_date=09-NOV-12 09.45.00.000000 AM -06:00 time zone is specified by absolute offset -6:00. So this job can't follow DST because as ORACLE doc say:
    The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.

    Job EVENT_TRANSFER, repeat_interval is in PL/SQL syntax, start_date=05-NOV-12 10.30.00.000000 AM -05:00. Because repeat_internal is in PL/SQL = function MON_TO_FRI then timezone for next_run_date is defined by this function. And this function use SYSDATE.

    A we can see SYSTIMESTAMP has now timezone -06:00. In DST it is hour shifted =-5:00.

    So what's conclusion:
    1/ when you are using PL/SQL syntax for repeat_interval then time zone is defined by used expression. In your case by function MON_TO_FRI. This function is based on SYSDATE and this function change timezone twice a year (DST start and end).
    2/ when you are using CANLENDAR syntax for repeat_interval and you want job to follow DST changes correctly you must specify start_date with timezone in region/city.
    3/ if you want to correct job JOB_PEOPLESOFT_EXTRA disable it, set start_date with correct timezone definition and then enable job

    dbms_scheduler.stime return 12-NOV-12 11.45.24.658242000 AM CST6CDT so I hope that CST6CDT is correct timezone of your DB. You profile says that you are located in TN - USA and UTC-6 is for Tennessee (middle and western)- Memphis dst.
  • 8. Re: DST questions, part duex
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    Job JOB_PEOPLESOFT_EXTRA, repeat_interval is in CALENDAR, syntax start_date=09-NOV-12 09.45.00.000000 AM -06:00 time zone is specified by absolute offset -6:00. So this job can't follow DST because as ORACLE doc say:
    The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.
    Yes, I'm with you so far.
    Job EVENT_TRANSFER, repeat_interval is in PL/SQL syntax, start_date=05-NOV-12 10.30.00.000000 AM -05:00. Because repeat_internal is in PL/SQL = function MON_TO_FRI then timezone for next_run_date is defined by this function. And this function use SYSDATE.
    And this is where I'm missing something. I don't see how the timezone is defined in the function whose code I provided. SYSDATE doesn't carry TZ info.

    And anticipating the next question ...
    SQL> DECLARE
      2   x VARCHAR2(100);
      3  BEGIN
      4    dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
      5    dbms_output.put_line('DTZ: ' || x);
      6  END;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> set serveroutput on
    SQL> /
    DTZ: CST6CDT
    
    PL/SQL procedure successfully completed.
    A we can see SYSTIMESTAMP has now timezone -06:00. In DST it is hour shifted =-5:00.

    So what's conclusion:
    1/ when you are using PL/SQL syntax for repeat_interval then time zone is defined by used expression. In your case by function MON_TO_FRI. This function is based on SYSDATE and this function change timezone twice a year (DST start and end).
    And that's what I'm failing to see -- HOW it is defined by the ps/sql function.
    2/ when you are using CANLENDAR syntax for repeat_interval and you want job to follow DST changes correctly you must specify start_date with timezone in region/city.
    3/ if you want to correct job JOB_PEOPLESOFT_EXTRA disable it, set start_date with correct timezone definition and then enable job
    Yes. Actually, my testing has shown no reason to disable the job first. I've seen that if it simply unset start date (dbms_scheduler.set_attribute_null ('ESTEVENS.EDST_TEST_3','START_DATE');), the TZ for START_DATE gets set to the DEFAULT_TIME_ZONE (see above) AND .... the TZ spec in NEXT_RUN_TIME gets reset as well. If REPEAT_INTERVAL is specified as an interval, NEXT_RUN_TIME gets adjusted to match the current time, but if it is specified as an absolute (BYDAY, BYHOUR, etc.) only the TZ info of NEXT_RUN_TIME will change.
    >
    dbms_scheduler.stime return 12-NOV-12 11.45.24.658242000 AM CST6CDT so I hope that CST6CDT is correct timezone of your DB. You profile says that you are located in TN - USA and UTC-6 is for Tennessee (middle and western)- Memphis dst.
    I've got the fix ready to implement and fully tested, and in addition to fixing the TZ info itself, we are going to replace the home-grown functions with standard calendar syntax. At this point I'm just trying to fill in the last bits of my understanding. Purely academic now.
  • 9. Re: DST questions, part duex
    spajdy Pro
    Currently Being Moderated
    Try
    SELECT CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE) FROM dual;
    you will see timezone.

    Or more detailed example
    ALTER SESSION SET TIME_ZONE='Europe/Prague';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    
    ALTER SESSION SET time_zone='Asia/Bangkok';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    
    ALTER SESSION SET time_zone='-6:00';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    You can see that DATE to TIMESTAMP WITH TIME ZONE convesion took TIME_ZONE from session.
  • 10. Re: DST questions, part duex
    EdStevens Guru
    Currently Being Moderated
    spajdy wrote:
    Try
    SELECT CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE) FROM dual;
    you will see timezone.
    Ah!

    And since there is an implied conversion (CAST) in the function -- using SYSDATE to eventually populate a TIMESTAMP WITH TIME ZONE ....

    Got it.

    Thanks for all your help.

    Or more detailed example
    ALTER SESSION SET TIME_ZONE='Europe/Prague';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    
    ALTER SESSION SET time_zone='Asia/Bangkok';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    
    ALTER SESSION SET time_zone='-6:00';
    SELECT dbms_scheduler.stime, sysdate, systimestamp, current_timestamp,CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE), CAST (to_date('1.1.2012','dd.mm.yyyy') AS TIMESTAMP WITH TIME ZONE) FROM dual;
    You can see that DATE to TIMESTAMP WITH TIME ZONE convesion took TIME_ZONE from session.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points