Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE

[raj malhotra]

Hi
I am using oracle 10 g as a database.I have created one table which has a column datataype as TIMESTAMP(6) WITH LOCAL TIME ZONE.But when i try to retrieve the the value from that using rs.getString() or rs.getTimestamp() i get error

java.sql.SQLException: Timezone not supported
	at oracle.sql.TIMESTAMPLTZ.getZoneOffset(TIMESTAMPLTZ.java:1840)
	at oracle.sql.TIMESTAMPLTZ.TimeZoneAdjust(TIMESTAMPLTZ.java:1721)
	at oracle.sql.TIMESTAMPLTZ.toTimestamp(TIMESTAMPLTZ.java:1151)
	at oracle.jdbc.driver.OracleStatement.getTimestampValue(OracleStatement.java:4792)
	at oracle.jdbc.driver.OracleResultSetImpl.getTimestamp(OracleResultSetImpl.java:663)
	at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java:1625)
	

Any body know how to fix it?

Thanks,
Raj

[John Kimball]

Without handling the underlying problem...

In the SQL query, convert the timestamp value to a char/varchar.
And then get it as .getString.

[Paul Sturrock]

You need to set the timezone for the driver before using this call. See the documentation.

[raj malhotra]

Thanks to both of you for reply.
Meanwhile I have found one more similar way to deal with it.I modified the sql query in this manner for example:

select  To_Timestamp_Tz(create_timestamp) from table1

Following is the result i got :

direct from db :  16-JUN-09 10.29.10.296000000 PM
to_char :         16-JUN-09 04.59.10.296000 PM
To_Timestamp_Tz : 2009-06-16 16:59:10.296
To_Timestamp    2009-6-16.16.59. 10. 296000000

Still i am not sure Why All results are not matching with the first one?

Thanks,
Raj

[John Kimball]

The timezone of the server hosting the JDBC code is different from the Oracle server's.

They're all the same time, except some are converting to the local (Java server's) timezone.

The question now is, is that what you want?

[raj malhotra]

The question now is, is that what you want?

I want that all the timestamp values should be stored in the db according to Oracle server time But when i show that timestamp on a website it should show according to the user's locale.The user may be in different timezone.

Thanks,
Raj

[Paul Sturrock]

If you set the timezone (as directed above) you will get this behaviour. From the documentation:

Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the OracleConnection.setSessionTimeZone(String regionName) method to set the session time zone. When this method is called, the JDBC driver sets the session time zone of the connection and saves the session time zone so that any TIMESTAMP WITH LOCAL TIME ZONE data accessed through JDBC can be adjusted using the session time zone.

[raj malhotra]

Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the OracleConnection.setSessionTimeZone(String regionName) method to set the session time zone.

Do i need to cast the Connection object to OracleConnection ?I didn't get this part.

Thanks,
Raj

[Paul Sturrock]

Yes. In order to use this Oracle specific feature you need to use the Oracle specific driver explicitly. A bit irritating, since the with timzeone stuff is part of the SQL99 standard but there you go.