Looking at Scheduled Jobs through the Schema Browser, the Info tab shows:
Start Date 25/08/2013 03:00:00.800000 -07:00
Schedule Name DAILY_PURGE_SCHEDULE
Repeat Interval [null]
Last Start Date 18/06/2019 03:00:00.737389 -07:00
Next Run Date 19/06/2019 03:00:00.739333 -07:00
DAILY_PURGE_SCHEDULE has the following:
Repeat Interval freq=daily;byhour=3;byminute=0;bysecond=0
Start Date [null]
The Run Log for the job shows for the last run:
Log Date 18/06/2019 11:00:31.393036 +01:00
Required Start Date 18/06/2019 03:00:00.686384 -07:00
Actual Start Date 18/06/2019 03:00:00.737479 -07:00
I think this is all consistent, but the Scheduled Run Times tab shows the next runs as:
19/06/2019 03:00:00.868208000 +01:00
20/06/2019 03:00:00.868208000 +01:00
21/06/2019 03:00:00.868208000 +01:00
All the other places show it running daily at 03:00 -7 but the Scheduled Run Times shows 03:00 +1. I'm in the UK which is currently UTC+1
Hi Paul.
I'm not withing many miles of any of my systems right now, so this is off the top of my head. Check the scheduler manual for the proper details!
I think the job's log shows the dbtimezone related times, hence the GMT -7 stuff. The run times are session time zone.
Also check your dbtimezone as if it is something like +01:00 you will never get daylight savings time aka British summer time, to get that you need to set it to "Europe/London" which does adjust for BST/GMT correctly.
Check the manual for dbtimezone for details.
This isn't a Toad problem it's Oracle.
HTH
Cheers,
Norm. [TeamT]
Hi Norm,
I'm not concerned that in some places it shows the time with different timezones. I'm quite happy that 03:00 -7 = 11:00 +1 which all equals 10:00 UTC. The bit that I think is wrong is the Scheduled Run Times which shows 03:00 +1 which is 02:00 UTC. I think this should either show 03:00 -7 or 11:00 +1.
By spooling the SQL to the screen, I can see it's calling dbms_scheduler.evaluate_calendar_string ('freq=daily;byhour=3;byminute=0;bysecond=0', ts_start, ts_return, ts_next);. The trouble is, it's using SYSTIMESTAMP for ts_start. SYSTIMESTAMP shows the UTC offset as +1, so all the results are calculated using that timezone. The job itself has a start_date with UTC offset -7, so I think it's this timezone that should be being passed to evaluate_calendar_string
Thanks,
Paul
Hey Paul,
So, I was playing with this, and for me, for some jobs it was using SYSTIMESTAMP and others it was using the actual start date.
We do have code in there for start date to be used for TS_START in the scheduled_run_time block. But we also had some code in there to look it up from the schedule if a named schedule existed. I made a change for next beta to always use start time if that info is present in the job, and only look in the schedule if that info isn't found in the job.
-John
Thanks John. I'm on holiday after today, so depending when the next beta is released, it might be a few days before I can try it, but I'll give you an update once I do...
This appears to be working in 13.2.0.173, thanks