This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
The moose likes JDBC and the fly likes Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE" Watch "Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE" New topic
Author

Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE

raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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



Any body know how to fix it?

Thanks,
Raj
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
raj malhotra
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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:


Following is the result i got :

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

Thanks,
Raj
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
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
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Feb 22, 2007
Posts: 285
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem with TIMESTAMP(6) WITH LOCAL TIME ZONE
 
Similar Threads
timezone conversion using DST
Timestamp Time Difference
Informix and ResultSet.getTimestamp()
mysql timestamp
TimeStamp.toString() does not work as expected