Use "to_timestamp_tz" when generating job SQL

[Patrick Holmes]

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'),
===============================

 

[Marco Kalter]

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.

 

[Patrick Holmes]

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

 

[Marco Kalter]

We'll check it out.

 

[IncredibleMrT]

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.

 

[Marco Kalter]

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 = '...').