Print Thread
Use "to_timestamp_tz" when generating job SQL
#47440 07/10/13 12:06 AM
Joined: Dec 2005
Posts: 55
P
Member
OP Offline
Member
P
Joined: Dec 2005
Posts: 55
Thank you for generating the SQL for DBMS_Scheduler jobs in version 10! That's really helpful. One small additional request:
We need to use the "to_timestamp_tz" function when creating jobs because we have daylight savings time (DST) here in the U.S. The jobs do not run at the correct times when DST changes unless we create the jobs with the timezone. So instead of generating this for start date:
===============================
start_date => to_date('09-07-2013 14:00:00', 'dd-mm-yyyy hh24:mi:ss'),
===============================
you should generate what is actually stored in the job:
====================================
start_date => to_timestamp_tz('AMERICA/LOS_ANGELES 14:00 09-JUL-2013', 'TZR HH24:MI DD-MON-RRRR'),
===============================

Re: Use "to_timestamp_tz" when generating job SQL
Patrick Holmes #47443 07/10/13 09:43 AM
Joined: Aug 1999
Posts: 22,220
Member
Offline
Member
Joined: Aug 1999
Posts: 22,220
You can enable the "Use timestamp format for scheduler objects" preference (Tools > Preferences > Oracle / Options). Now the timestamp properties will be formatted in accordance with the NLS_TIMESTAMP_TZ_FORMAT of your session.


Marco Kalter
Allround Automations
Re: Use "to_timestamp_tz" when generating job SQL
Marco Kalter #48108 11/02/13 01:22 AM
Joined: Dec 2005
Posts: 55
P
Member
OP Offline
Member
P
Joined: Dec 2005
Posts: 55
I tried checking that (Tools > Preferences > Oracle / Options) option, but then when I used DBMS-Scheduler and clickec on [View SQL] button, I got this error message: "Could not convert variant of type (String) into type (Double)"

If I un-check that option, I get this for the start-date for a job:
"start_date => to_date('12-03-2013 12:50:00', 'dd-mm-yyyy hh24:mi:ss'),"

Here is what I was expecting:
"start_date => to_timestamp_tz('AMERICA/LOS_ANGELES 12-03-2013 13:50', 'TZR DD-MM-RRRR HH24:MI'),"

Did I do something wrong? Please advise.
thanks,
pat

Re: Use "to_timestamp_tz" when generating job SQL
Patrick Holmes #48113 11/04/13 01:33 PM
Joined: Aug 1999
Posts: 22,220
Member
Offline
Member
Joined: Aug 1999
Posts: 22,220
We'll check it out.


Marco Kalter
Allround Automations
Re: Use "to_timestamp_tz" when generating job SQL
Marco Kalter #59953 04/18/19 01:52 PM
Joined: Jun 2007
Posts: 61
Sheffield
I
Member
Offline
Member
I
Joined: Jun 2007
Posts: 61
Sheffield
Sorry for resurrecting such an old thread - did this ever get sorted?
We have the same issue as the above. We're in the UK and outside of BST things create and work as expected.
Then, come spring, everything is an hour out.
I can fix by modifying all job's start_date using to_timestamp_tz(..) but I expected the job creation aspect of the GUI would do that for me - picking up from my OS region settings.

I would expect the region to set to 'Europe/London' but it does not. I have the checkbox you mentioned ticked.
Thank you.

Re: Use "to_timestamp_tz" when generating job SQL
IncredibleMrT #59956 04/19/19 10:04 AM
Joined: Aug 1999
Posts: 22,220
Member
Offline
Member
Joined: Aug 1999
Posts: 22,220
PL/SQL Developer will use the database timezone by default. To change the default timezone you can add change the database timezone (alter database set time_zone = '...') and shutdown/restart the database instance, or you can set the session time zone in the AfterConnect.sql script in the PL/SQL Developer installation directory (alter session set time_zone = '...').



Marco Kalter
Allround Automations

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.037s Queries: 14 (0.013s) Memory: 2.5217 MB (Peak: 3.0424 MB) Data Comp: Off Server Time: 2024-05-18 16:19:13 UTC
Valid HTML 5 and Valid CSS