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@xxxxxxx<mailto:srcdco@xxxxxxx> | 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@xxxxxxxxx>
Sent: Wednesday, January 26, 2022 6:17 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha<http://twitter.com/kibeha>
On Tue, Jan 25, 2022 at 9:44 PM Scott Canaan
<srcdco@xxxxxxx<mailto:srcdco@xxxxxxx>> 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@xxxxxxx<mailto:srcdco@xxxxxxx> | 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.
