CODEWITHSUNDEEP
timezoneoracle-sqldevelopersqlplusoracle12c
Pedro
Pedro

Reputation: 41

ORA-01857 when executing to_timestamp_tz()

Why executing this query over SQLDeveloper to connect to my database:

select to_timestamp_tz('05/22/2016 10:18:01 PDT', 'MM/DD/YYYY HH24:MI:SS TZD') from dual;

I get the following error:

ORA-01857: "not a valid time zone"
01857. 00000 -  "not a valid time zone"
*Cause:    
*Action:

But, I'm able to execute the query without any error directly from sqlplus on the host where the database is located, getting the expected result:

TO_TIMESTAMP_TZ('05/22/201610:18:01PDT','MM/DD/YYYYHH24:MI:SSTZD')
---------------------------------------------------------------------------
22-MAY-16 10.18.01.000000000 AM -07:00

So, I'm trying to figure out if I'm doing something incorrectly. I have read that error could be cause because of multiple tzabbrev for a timezone, but this does not explains why on sqlplus runs the query correctly, since I can see the multiple tzabbrev for different time regions on both host and SQLDeveloper (query from v$timezone_names).

The real issue is that our application uses this query, so we notice that this issue reproduces sometimes, even if the application is deploy on the same host as the database.

Upvotes: 0

Views: 1761

Answers (1)

thanh ngo
thanh ngo

Reputation: 844

I add 2 new lines to sqldeveloper\sqldeveloper\bin\sqldeveloper.conf

AddVMOption -Doracle.jdbc.timezoneAsRegion=false
AddVMOption -Duser.timezone=CET

and this fix the problem.

Updated

To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element

To make your query work without changing anything from the JVM configuration, you should provide the timezone region

select to_timestamp_tz('05/22/2016 10:18:01 PDT US/Pacific', 'MM/DD/YYYY HH24:MI:SS TZD TZR') from dual;

Because you didn't provide the timezone region, it will get the default one. Let's look at the first parameter 'oracle.jdbc.timezoneAsRegion'. This is defined by the jdbc driver as follow:

CONNECTION_PROPERTY_TIMEZONE_AS_REGION

Use JVM default timezone as specified rather than convert to a GMT offset. Default is true.

So without defining this property, you force your query to use the default timezone region defined by property 'user.timezone'. But actually you haven't set it yet. So the solution is either you set the property 'oracle.jdbc.timezoneAsRegion' to false (and the database current session time zone region will be used) or provide the it implicitly with 'user.timezone' property

Upvotes: 1

Related Questions