Re: dbms_scheduler_job_run_details Question

Hmm... That output would mean that LOG_DATE also is not in DBTIMEZONE? Okay, that might then be the SESSIONTIMEZONE of the session in which the job runs, which might be taken from the OS time zone. Not quite sure about that.

What is output of select dbtimezone from dual?

You could do AT TIME ZONE DBTIMEZONE on *all three* columns (instead of just the *_START_DATE columns).
Or if you prefer you could use AT TIME ZONE SESSIONTIMEZONE to get the output in the time zone of your session where you execute the query. Or use AT TIME ZONE 'EST' to have it always fixed in Eastern Standard. Or use AT TIME ZONE 'America/New_York' if you want an output that follows DST on/off.

But at least the output shows that it started 05:30:01 in GMT-4, which is 04:30:01 in GMT-5.
Then it stopped a minute later at 04:31:04 in GMT-5, which is the same as 05:31:04 in GMT-5.

Users say it is supposed to run at 05:30. That *is *also what it does - it *does* run at 05:30, which the repeat_interval is stating.
But it is running at 05:30 in *GMT-4*, which is 04:30 in GMT-5. So if the users are expecting it to run at 05:30 in GMT-5, then it is not running at the time the users are expecting it.

Try the:

select

   to_char(start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as start_date from dba_scheduler_jobs
where job_name = 'RIT_K0704_BL_GOALS_DBMS_JOB';

The repeat interval will be used to create starting times in the TZR of the start_date of the job.

My guess would be that the TZR of the job is -04:00. This could happen if DST was active (EDT=GMT-4 instead of EST=GMT-5) at the time when the job was created, and a DATE datatype was used in the CREATE_JOB call instead of a TIMESTAMP WITH TIME ZONE.

If you can see that the job has -04:00 as TZR, then I suggest dropping the job and re-creating it with something like:

begin

   dbms_scheduler.create_job (

      job_name             => 'RIT_K0704_BL_GOALS_DBMS_JOB'
    , job_type             => {your values}
    , job_action           => {your values}
    , start_date           => TIMESTAMP '2022-01-27 05:30:00
 America/New_York'
    , repeat_interval      => 'Freq=Daily;ByHour=05;ByMinute=30'
    , job_class            => {your values}
    , enabled              => {your values}
    , auto_drop            => {your values}
    , comments             => {your values}

By explicitly stating America/New_York you will get that the job executes at 05:30 EST = GMT-5 currently, but 05:30 EDT = GMT-4 when DST is in effect.

Of course, that's only if this is what the users wish. If they want 05:30 GMT-5 all year round, then that's what you specify instead of America/New_York.

Is that more clear?

Cheerio
/Kim

On Wed, Jan 26, 2022 at 1:36 PM Scott Canaan <srcdco_at_rit.edu> wrote:

> Kim,
>
> Interesting. When I run the query near the end that includes the “at
> time zone dbtimezone”, I get the following output:
>
>
>
> LOG_DATE
> REQ_START_DATE_DB_TZ ACTUAL_START_DATE_DB_TZ
>
> 2022-01-25 04:31:04.577 -05:00 2022-01-25 05:30:00.129
> -04:00 2022-01-25 05:30:01.753 -04:00
>
>
>
> The reason this came up is that the customer has multiple jobs, running
> under different schedulers, that depend on one another. Yesterday they
> noticed that the data from another job that supposedly completed at 5:23am
> was not actually in the database before this job ran. So they are saying
> that the log_date is telling them that the job didn’t run at its scheduled
> time of 5:30am, but actually ran at 4:30am.
>
>
>
> I don’t see how that could happen, since the job definition has this as
> the repeat_interval:
>
>
>
> Freq=Daily;ByHour=05;ByMinute=30
>
>
>
> *Scott Canaan ‘88*
>
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
> *From:* Kim Berg Hansen <kibeha_at_gmail.com>
> *Sent:* Wednesday, January 26, 2022 6:17 AM
> *To:* Scott Canaan <srcdco_at_rit.edu>
> *Cc:* oracle-l_at_freelists.org
> *Subject:* Re: dbms_scheduler_job_run_details Question
>
>
>
> Hi, Scott
>
>
>
> As mentioned by Andy, the columns (although named *_DATE) are not DATE
> datatype, they are timestamps with time zone.
>
>
>
> Logging is in the database time zone, start dates are in the time zone
> specified for each specific job.
>
>
>
> Example:
>
>
>
> Database is running GMT:
>
>
>
> select dbtimezone from dual;
>
>
>
> DBTIME
>
> ------
>
> +00:00
>
>
> The job was created using an original start date in Europe/Amsterdam:
>
>
>
> select
>
> to_char(start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as start_date
>
> from dba_scheduler_jobs
>
> where job_name = 'MYJOB';
>
>
>
> START_DATE
>
> --------------------------------------------------------
>
> 2021-11-25 15:15:00.000 EUROPE/AMSTERDAM
>
>
> The time zones of the job run details show database time zone for
> LOG_DATE, job time zone for *_START_DATE:
>
>
>
> select
>
> to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
>
> , to_char(req_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
> req_start_date
>
> , to_char(actual_start_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as
> actual_start_date
>
> from dba_scheduler_job_run_details
>
> where job_name = 'MYJOB'
>
> order by log_date desc fetch first 1 row only;
>
>
>
> LOG_DATE REQ_START_DATE
> ACTUAL_START_DATE
>
>
> --------------------------------------------------------
> --------------------------------------------------------
> --------------------------------------------------------
>
> 2022-01-26 10:30:07.863 +00:00 2022-01-26
> 11:30:00.673 EUROPE/AMSTERDAM 2022-01-26 11:30:00.800
> EUROPE/AMSTERDAM
>
>
>
> Amsterdam is currently GMT+1, so we see that the job actually started at
> 10:30:00.800 GMT and logged when it ended at 10:30:07.863 GMT.
>
>
>
> If you want to display output of job run details in DB time zone alone (to
> make it easier for humans to compare), you can use the AT TIME ZONE syntax,
> like for example this:
>
>
>
> select
>
> to_char(log_date, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR') as log_date
>
> , to_char(req_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
> HH24:MI:SS.FF3 TZR') as req_start_date_db_tz
>
> , to_char(actual_start_date AT TIME ZONE DBTIMEZONE, 'YYYY-MM-DD
> HH24:MI:SS.FF3 TZR') as actual_start_date_db_tz
>
> from dba_scheduler_job_run_details
>
> where job_name = 'MYJOB'
>
> order by log_date desc fetch first 1 row only;
>
>
>
> LOG_DATE
> REQ_START_DATE_DB_TZ
> ACTUAL_START_DATE_DB_TZ
>
> --------------------------------------------------------
> --------------------------------------------------------
> --------------------------------------------------------
>
> 2022-01-26 10:30:07.863 +00:00 2022-01-26
> 10:30:00.673 +00:00 2022-01-26 10:30:00.800
> +00:00
>
>
>
>
>
> Notice also, that in a case like this, where DB is running GMT, the time
> difference for Amsterdam is currently 1 hour - when Daylight Savings Time
> kick in, the time difference will be 2 hours.
>
> That means also, that if you want jobs to follow DST, you must not use a
> DATE when you create jobs if your DB time zone is not a DST aware time zone.
>
> The safest way to create jobs is to use a timestamp with explicit time
> zone, like for example:
>
>
>
> begin
>
> dbms_scheduler.create_job (
>
> job_name => 'MYJOB'
>
> , job_type => 'PLSQL_BLOCK'
>
> , job_action => 'mypackage.myproc(42);'
>
> , start_date => TIMESTAMP '2020-11-06 01:30:00
> EUROPE/AMSTERDAM'
>
> , repeat_interval => 'freq=daily; byhour=1; byminute=30;'
>
> , job_class => 'MY_JOB_CLASS'
>
> , enabled => false
>
> , auto_drop => false
>
> , comments => 'Job to do something every night at 01:30'
>
> );
>
> end;
>
> /
>
>
>
> This will ensure the job runs at 01:30 Amsterdam time, which will be GMT+1
> in the winter and GMT+2 in the summer.
>
> If I had used a DATE for the START_DATE parameter, it would have been
> converted to a timestamp in DB timezone, which might be a non-DST aware
> time zone.
>
>
>
> On the other hand if I always want a job to start a given time GMT no
> matter what time of year it is and ignore DST, then I could just use:
>
>
>
> , start_date => TIMESTAMP '2020-11-06 01:30:00 GMT'
>
>
>
> Again specifying exactly what I want a job creation, rather than relying
> on DB time zone.
>
>
>
>
>
> I hope that helps ;-)
>
>
>
> Cheerio
>
> /Kim
>
>
>
>
>
> Regards
>
>
>
>
>
> Kim Berg Hansen
>
> Senior Consultant at Trivadis - Part of Accenture
>
> Oracle ACE Director
>
>
>
> Author of Practical Oracle SQL
> <https://www.apress.com/gp/book/9781484256169>
>
> http://www.kibeha.dk
>
> kibeha_at_kibeha.dk
>
> _at_kibeha <http://twitter.com/kibeha>
>
>
>
>
>
> On Tue, Jan 25, 2022 at 9:44 PM Scott Canaan <srcdco_at_rit.edu> wrote:
>
> I got an inquiry from a customer about the dates in the
> dbms_scheduler_job_run_details view. He is confused as to why the log_date
> is an hour earlier than the req_start_date and actual_start_date. For
> example:
>
>
>
> Log_id log_date
> Job_name
> requested_start_date actual_start_date
>
> 8610130 1/25/2022 4:31:05 AM
> RIT_K0704_BL_GOALS_DBMS_JOB 1/25/2022 5:30:00 AM
> 1/25/2022 5:30:02 AM
>
>
>
> The requested start time is set in the job definition to be 5:30am. I’m
> having trouble explaining why there’s a difference. He has asked me to
> “fix the GMT differential”.
>
>
>
> This is an Oracle 12.1.0.2 database running on Red Hat 7.
>
>
>
>
> *Scott Canaan ‘88 *
> *Sr Database Administrator *Information & Technology Services
> Finance & Administration
>
>
> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>
> *srcdco_at_rit.edu <srcdco_at_rit.edu>* | c: (585) 339-8659
>
> *CONFIDENTIALITY NOTE*: The information transmitted, including
> attachments, is intended only for the person(s) or entity to which it is
> addressed and may contain confidential and/or privileged material. Any
> review, retransmission, dissemination or other use of, or taking of any
> action in reliance upon this information by persons or entities other than
> the intended recipient is prohibited. If you received this in error, please
> contact the sender and destroy any copies of this information.
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l