Kim,
Here’s the output from the first SQL (select dbtimezone from dual):
-04:00
If I add the AT TIME ZONE DBTIMEZONE to the log_date, then all the times are
the same (5:30am).
2022-01-25 05:31:04.577 -04:00 2022-01-25 05:30:00.129 -04:00
2022-01-25 05:30:01.753 -04:00
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 8:53 AM
To: Scott Canaan <srcdco@xxxxxxx>
Cc: oracle-l@xxxxxxxxxxxxx
Subject: 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}
);
end;
/
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@xxxxxxx<mailto:srcdco@xxxxxxx>> 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@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<mailto:kibeha@xxxxxxxxx>>
Sent: Wednesday, January 26, 2022 6:17 AM
To: Scott Canaan <srcdco@xxxxxxx<mailto:srcdco@xxxxxxx>>
Cc: oracle-l@xxxxxxxxxxxxx<mailto: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.
