I have been trying to get timestamps with timezones working in my Java application, but I don't seem to get the data I expect back from the JDBC driver.
I inserted a date (9/1/1978 7:00PM GMT-00:00) by doing a standard insert statement via the statement.execute( String ) method and the value appears correctly in the database:
01-SEP-78 07.00.00.000000 PM +00:00
However, when I try to read it out, I get unexpected behavior. I have tried a number of ways to get this working and so far the best answer I found is the following:
OracleResultSet oracleResultSet = (OracleResultSet)rs;
final TIMESTAMPTZ timestamptz = oracleResultSet.getTIMESTAMPTZ( idx );
Timestamp timestamp = timestamptz.timestampValue( connection );
Date date = new java.util.Date( timestamp.getTime() );
This ends up giving me back '9/1/1978 7:00PM GMT-07:00' (essentially adding my current timezone to the stored timestamp -- the underlying value is milliseconds from the epoch, which is what I would hope for, but somehow my millisecond offset from UTC is being added to this value before it is returned to my app).
I believe I have avoided this issue in the past by setting my connection properties to "UTC" and making sure that all dates that go into the TimestampTZ are in UTC as well, but this seems like a hack.
I am using Java 1.4.2_05 with the Oracle Thin Driver from the ojdbc14.jar and Oracle 10g.
Any advice would be appreciated.