[How To] Handle Oracle TimeStamp with TimeZone from Java

Oracle TimeStamp with TimeZone: A Critical Analysis

For storing the TimeZone information in TimeStamp, Oracle (10g) has two options:

  1. TIMESTAMP WITH TIME ZONE data type:  As the name indicates, this stores the TimeZone data along with the TimeStamp. The TimeZone can be either stored in the UTC format (eaxmple: +05:30) or the TimeZoneRegion format (example: Asia/Calcutta or Indian Standard Time).
  2. TIMESTAMP WITH LOCAL TIME ZONE data type: This does not store the TimeZone information per se. While inserting data, it adjusts for the offset of the client’s TimeZone and stores it according to the db server’s local TimeZone, without the actual TimeZone information. Similarly, while querying, the TimeStamp will be offseted against the client’s TimeZone, without returning any explicit TimeZone information. This data type is very confusing and is to be avoided at all costs.

As far as JDBC is concerned, things are plain and simple when we talk about Statement. The trouble starts the moment we think about PreparedStatement. Both these data types map to the java.sql.Timestamp in JDBC. But java.sql.Timestamp does not have any TimeZone information. In this scenario, we should be using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) to pass the TimeZone information to the db driver. There is another twist to this tale. The TIMESTAMP WITH LOCAL TIME ZONE data type needs to adjust the time against the client’s TimeZone. When we open a Oracle client session, the TimeZone is already set. But that is not the case with JDBC Connection. The TimeZone info is not set by the driver, for reasons best known to themselves. So a simple ResultSet.getTimestamp(int columnIndex) will not work. Instead, we have to use the ResultSet.getTimestamp(int columnIndex, Calendar cal) to pass the TimeZone to the db driver.

Simple JDBC example

Consider the following table having both types of TimeStamp with TimeZone:

This is the SQL statement for inserting data:

INSERT INTO TIMESTAMP_DEMO (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) VALUES (0, ‘manualInsert’, TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’), TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’));

We will try to insert data from plain JDBC and then read it back to ensure that the information persisted is correct.

INSERT Approach 1

Consider the following code snippet:

Now run the following query from any Oracle SQL Client:

SELECT TO_CHAR(TIME_WITH_ZONE, ‘YYYY-MM-DD HH24:MI:SS:FF TZR’) AS TIME_WITH_ZONE FROM TIMESTAMP_DEMO;

You will find that the returned value is:

2010-11-02 11:30:00:000000000 +05:30

Note that the TimeZone info is wrongly stored. The only conclusion that we can have is that the JDBC driver is buggy. How do we get around this problem?

INSERT Approach 2

On running the select statement, the data got back is:

2010-11-02 11:30:00:000000000 AUSTRALIA/ADELAIDE

This is precisely the data which was inserted. The trcik here is to bypass the JDBC data and use Oracle function TO_TIMESTAMP_TZ(timeString, format).

SELECT Approach 1

On running the above, we get the following exception:

java.sql.SQLException: The database session time zone is not set
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)

This is got on the line

res.getTimestamp(“TIME_WITH_ZONE_LOCAL”);

as the TimeZone is not set in the JDBC Connection object.

SELECT Approach 2

And this is how getTimeWithZone() looks like:

We get the following sysouts:

insert_1: 1, TIME=11:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time
insert_2: 2, TIME=06:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time

Note that in both cases, the returned TimeZone is wrongly fetched. Again, the JDBC driver is buggy.

SELECT Approach 3

As with INSERT, we will try to bypass the JDBC driver as follows:

The following sysouts are got:

insert_1: 1, TIME=11:30:00:000000000 +05:30, TIME_LOCAL=11:30:00:000000000 +05:30
insert_2: 2, TIME=11:30:00:000000000 AUSTRALIA/ADELAIDE, TIME_LOCAL=11:30:00:000000000 +05:30

Note that the values returned are exactly as intended.

Hibernate Example

Let us try and translate the above code into Hibernate. The first inference that we can draw from the above discussion is that get() on TIMESTAMP WITH LOCAL TIME ZONE will not work with the usual approach, and we have to define our custom data type to customise get() on PreparedStatement.

Hibernate Approach 1

This is how my custom data type (to map to TIMESTAMP WITH LOCAL TIME ZONE) looks like:

And this is how my entity looks like:

As expected, this will not save/get correct TimeZone data as it depends on the buggy JDBC driver.

Hibernate Approach 2

Here, as before, we use the TO_TIMESTAMP_TZ() function to circumvent the buggy JDBC driver. We keep the TimestampType for mapping to TIMESTAMP WITH LOCAL TIME ZONE and add TimestampType2 to map to TIMESTAMP WITH TIME ZONE.

And this is how my modified entity looks like:

This set up gives us the desirable save/get results with the correct TimeZone value.

Conclusion

I take this opportunity to illustrate the advantage of Open Source Software as opposed to proprietary ones. If the ojdbc sources were in public domain, we could have patched the bug instead of having to circumvent them as in the present instance.

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/OracleTimeZoneTest.

There are three test cases which illustrates the above discussion. I have used the ojdbc driver 5 for this example. Since I am using Maven for building this, the libraries are not a part of this distribution. You can grab any Oracle JDBC driver from here.

3 thoughts on “[How To] Handle Oracle TimeStamp with TimeZone from Java

  1. Hi there,
    Many thanks to give a detailed analysis on the oracle Timestamp with Timezone in a very easy way.
    I have very similar type of implementation is there but, I am getting a null pointer when fetching a column with value “09-NOV-05 12.00.00.000000 PM AMERICA/TORONTO”, I analyzed and found it behaves the same for some EST Timezones. Please let me know if you have any input on this.. and how can i resolve it.

    java.lang.NullPointerException
    at oracle.jdbc.driver.TimestamptzAccessor.getString(TimestamptzAccessor.java:167)
    at oracle.jdbc.driver.T4CTimestamptzAccessor.getString(T4CTimestamptzAccessor.java:278)
    at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1297)
    at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getString(WSJdbcResultSet.java:2461)
    at com.ibm.phsdsm.common.code.persistence.hibernate.PanoramaCalendar.nullSafeGet(PanoramaCalendar.java:162)

    Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *