[How To] Handle Oracle TimeStamp with TimeZone from Java

Posted by {"name"=>"Palash Ray", "email"=>"paawak@gmail.com", "url"=>"https://www.linkedin.com/in/palash-ray/"} on November 01, 2010 · 15 mins read

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:

CREATE TABLE TIMESTAMP_DEMO (
	ID                                                    NUMBER(6) PRIMARY KEY NOT NULL,
	NAME                                             VARCHAR2(20) NOT NULL,
	TIME_WITH_ZONE                     TIMESTAMP(9) WITH TIME ZONE NOT NULL,
	TIME_WITH_ZONE_LOCAL      TIMESTAMP(9) WITH LOCAL TIME ZONE
    ) ;

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:

        PreparedStatement pStat = con
                .prepareStatement("INSERT INTO TIMESTAMP_DEMO "
                        + " (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) "
                        + " VALUES " + " (?, ?, ?, ?)");
        pStat.setInt(1, 1);
        pStat.setString(2, "insert_1");
        String timeZoneId = "Australia/Adelaide";
        TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);
        Calendar timeWithZone = Calendar.getInstance(timeZone);
        timeWithZone.set(Calendar.HOUR_OF_DAY, 11);
        timeWithZone.set(Calendar.MINUTE, 30);
        timeWithZone.set(Calendar.SECOND, 0);
        timeWithZone.set(Calendar.MILLISECOND, 0);
        Timestamp ts = new Timestamp(timeWithZone.getTimeInMillis());
        pStat.setTimestamp(3, ts, timeWithZone);
        pStat.setTimestamp(4, ts, timeWithZone);
        pStat.execute();
        pStat.close();

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

        String timeZoneId = "Australia/Adelaide";
        TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);
        Calendar timeWithZone = Calendar.getInstance(timeZone);
        timeWithZone.set(Calendar.HOUR_OF_DAY, 11);
        timeWithZone.set(Calendar.MINUTE, 30);
        timeWithZone.set(Calendar.SECOND, 0);
        timeWithZone.set(Calendar.MILLISECOND, 0);
        String dateFormat = "yyyy-MM-dd HH:mm:ss:SSS";
        DateFormat df = new SimpleDateFormat(dateFormat);
        // this is very important
        if (timeZone != null) {
            df.setTimeZone(timeZone);
        }
        String dateTime = df.format(timeWithZone.getTime());
        String tzId = timeWithZone.getTimeZone().getID();
        dateTime += " " + tzId;
        PreparedStatement pStat = con
                .prepareStatement("INSERT INTO TIMESTAMP_DEMO "
                        + " (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) "
                        + " VALUES "
                        + " (?, ?, TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS:FF TZR'), ?)");
        pStat.setInt(1, 2);
        pStat.setString(2, "insert_2");
        Timestamp ts = new Timestamp(timeWithZone.getTimeInMillis());
        pStat.setString(3, dateTime);
        pStat.setTimestamp(4, ts, timeWithZone);
        pStat.execute();
        pStat.close();

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

        Statement stat = con.createStatement();
        ResultSet res = stat
                .executeQuery("SELECT * FROM TIMESTAMP_DEMO  ORDER BY ID");
        while (res.next()) {
            Timestamp timestamp = res.getTimestamp("TIME_WITH_ZONE");
            Timestamp timestampLocal = null;
            try {
                timestampLocal = res.getTimestamp("TIME_WITH_ZONE_LOCAL");
            } catch (SQLException e) {
                e.printStackTrace();
                break;
            }
            System.out.println("TIME=" + timestamp + ", TIME_LOCAL="
                    + timestampLocal);
        }
        stat.close();
        res.close();

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

        Statement stat = con.createStatement();
        ResultSet res = stat
                .executeQuery("SELECT * FROM TIMESTAMP_DEMO  ORDER BY ID");
        while (res.next()) {
            Timestamp timestamp = res.getTimestamp("TIME_WITH_ZONE");
            Timestamp timestampLocal = res.getTimestamp("TIME_WITH_ZONE_LOCAL",
                    new GregorianCalendar(TimeZone.getDefault()));
            System.out.println(res.getString("NAME") + ": "
                    + res.getString("ID") + ", TIME="
                    + getTimeWithZone(timestamp) + ", TIME_LOCAL="
                    + getTimeWithZone(timestampLocal));
        }
        stat.close();
        res.close();

And this is how getTimeWithZone() looks like:

    public static String getTimeWithZone(Timestamp timestamp) {
        Calendar cal = new GregorianCalendar();
        cal.setTime(timestamp);
        return getTimeWithZone(cal);
    }
    public static String getTimeWithZone(Calendar cal) {
        String dateFormat = "HH:mm:ss:SSS zzzz";
        DateFormat df = new SimpleDateFormat(dateFormat);
        // this is very important
        TimeZone timeZone = cal.getTimeZone();
        if (timeZone != null) {
            df.setTimeZone(timeZone);
        }
        String dateTime = df.format(cal.getTime());
        return dateTime;
    }

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:

        Statement stat = con.createStatement();
        ResultSet res = stat.executeQuery("SELECT ID, NAME, "
                + " TO_CHAR(TIME_WITH_ZONE, 'HH24:MI:SS:FF TZR'), "
                + " TO_CHAR(TIME_WITH_ZONE_LOCAL, 'HH24:MI:SS:FF TZR') "
                + " FROM TIMESTAMP_DEMO  ORDER BY ID");
        while (res.next()) {
            String timestamp = res.getString(3);
            String timestampLocal = res.getString(4);
            System.out.println(res.getString("NAME") + ": "
                    + res.getString("ID") + ", TIME=" + timestamp
                    + ", TIME_LOCAL=" + timestampLocal);
        }
        stat.close();
        res.close();

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:

public class TimestampType implements UserType {
    // private static final Logger LOG = Logger.getLogger(TimestampType.class);
    @Override
    public int[] sqlTypes() {
        return new int[] { Types.TIMESTAMP };
    }
    @Override
    public Class returnedClass() {
        return Calendar.class;
    }
    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == null || y == null) {
            return false;
        }
        return x.equals(y);
    }
    @Override
    public int hashCode(Object x) throws HibernateException {
        if (x != null) {
            return x.hashCode();
        }
        return 0;
    }
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        Calendar cal = new GregorianCalendar();
        Timestamp timestamp = rs.getTimestamp(names[0], cal);
        if (timestamp != null) {
            cal.setTime(timestamp);
        } else {
            cal = null;
        }
        return cal;
    }
    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.DATE);
        } else {
            doInstanceCheck(value);
            Calendar cal = (Calendar) value;
            Timestamp timestamp = new Timestamp(cal.getTimeInMillis());
            st.setTimestamp(index, timestamp, cal);
        }
    }
    @Override
    public Object deepCopy(Object value) throws HibernateException {
        Calendar clone = null;
        if (value != null) {
            doInstanceCheck(value);
            Calendar cal = (Calendar) value;
            // just copying the timezone and time
            clone = new GregorianCalendar();
            clone.setTimeInMillis(cal.getTimeInMillis());
            TimeZone tz = cal.getTimeZone();
            clone.setTimeZone(TimeZone.getTimeZone(tz.getID()));
        }
        return clone;
    }
    @Override
    public boolean isMutable() {
        return true;
    }
    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        Calendar cal = null;
        if (value != null) {
            doInstanceCheck(value);
            cal = (Calendar) deepCopy(value);
        }
        return cal;
    }
    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return disassemble(cached);
    }
    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return disassemble(original);
    }
    protected void doInstanceCheck(Object value) {
        if ((value != null) && !(value instanceof Calendar)) {
            throw new UnsupportedOperationException(value.getClass()
                    + " not supported, expecting type "
                    + Calendar.class.getName());
        }
    }
}

And this is how my entity looks like:

@Entity
@Table(name = "TIMESTAMP_DEMO")
public class TimestampDemo implements Serializable {
    private static final long serialVersionUID = -5902132666472097299L;
    private long id;
    private String name;
    private Calendar timeWithZone;
    private Calendar timeWithZoneLocal;
    @Id
    @SequenceGenerator(name = "seq", sequenceName = "TIMESTAMP_DEMO_SEQ")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
    public long getId() {
        return id;
    }
    @Column
    public String getName() {
        return name;
    }
    @Column(name = "TIME_WITH_ZONE")
    @Temporal(TemporalType.TIMESTAMP)
    public Calendar getTimeWithZone() {
        return timeWithZone;
    }
    @Column(name = "TIME_WITH_ZONE_LOCAL")
    @Type(type = "com.swayam.demo.oracle.hibernate.custom.TimestampType")
    public Calendar getTimeWithZoneLocal() {
        return timeWithZoneLocal;
    }
    public void setId(long id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setTimeWithZone(Calendar timeWithZone) {
        this.timeWithZone = timeWithZone;
    }
    public void setTimeWithZoneLocal(Calendar timeWithZoneLocal) {
        this.timeWithZoneLocal = timeWithZoneLocal;
    }
}

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.

public class TimestampType2 extends TimestampType {
    private static final Logger LOG = Logger.getLogger(TimestampType2.class);
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        Calendar cal = null;
        String timestamp = rs.getString(names[0]);
        LOG.info("raw timestamp=" + timestamp);
        if (timestamp != null) {
            cal = parseOracleTimestampWithZone(timestamp);
        }
        return cal;
    }
    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.DATE);
        } else {
            doInstanceCheck(value);
            Calendar cal = (Calendar) value;
            String dateTime = getOracleFormattedTimeWithZone(cal);
            LOG.info("dateTime=" + dateTime);
            LOG.info("index=" + index);
            st.setString(index, dateTime);
        }
    }
    /**
     * Converts raw time-stamp with time-zone string from Oracle to Calendar
     * containing the time-zone
     *
     * @param rawTimestamp
     *            in the format 2010-9-26 11.30.0.0 Australia/Adelaide
     * @return
     */
    private static Calendar parseOracleTimestampWithZone(String rawTimestamp) {
        Calendar cal = null;
        String dateFormat = "yyyy-MM-dd HH.mm.ss.SSS";
        SimpleDateFormat df = new SimpleDateFormat(dateFormat);
        try {
            Date date = df.parse(rawTimestamp);
            Calendar tempCal = Calendar.getInstance();
            tempCal.setTime(date);
            String timeZoneId = rawTimestamp.split("s")[2];
            TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);
            cal = new GregorianCalendar(timeZone);
            // setting the date and time-zone does not work, as Calendar adjusts
            // for time zone, so the below circus
            cal.set(Calendar.YEAR, tempCal.get(Calendar.YEAR));
            cal.set(Calendar.MONTH, tempCal.get(Calendar.MONTH));
            cal.set(Calendar.DATE, tempCal.get(Calendar.DATE));
            cal.set(Calendar.HOUR_OF_DAY, tempCal.get(Calendar.HOUR_OF_DAY));
            cal.set(Calendar.MINUTE, tempCal.get(Calendar.MINUTE));
            cal.set(Calendar.SECOND, tempCal.get(Calendar.SECOND));
            cal.set(Calendar.MILLISECOND, tempCal.get(Calendar.MILLISECOND));
        } catch (ParseException e) {
            LOG.error("Could not convert string `" + rawTimestamp
                    + "` to Calendar", e);
        }
        return cal;
    }
    private static String getOracleFormattedTimeWithZone(Calendar timeWithZone) {
        String dateFormat = "yyyy-MM-dd HH:mm:ss:SSS";
        DateFormat df = new SimpleDateFormat(dateFormat);
        // this is very important
        TimeZone timeZone = timeWithZone.getTimeZone();
        if (timeZone != null) {
            df.setTimeZone(timeZone);
        }
        String dateTime = df.format(timeWithZone.getTime());
        String tzId = timeWithZone.getTimeZone().getID();
        dateTime += " " + tzId;
        return dateTime;
    }
}

And this is how my modified entity looks like:

@SQLInsert(sql = "INSERT INTO TIMESTAMP_DEMO (NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL, ID) values (?, TO_TIMESTAMP_TZ(?, 'YYYY-MM-DD HH24:MI:SS:FF TZR'), ?, ?)")
@Table(name = "TIMESTAMP_DEMO")
@Entity
public class TimestampDemo2 implements Serializable {
    private static final long serialVersionUID = 4940963602672391841L;
    private long id;
    private String name;
    private Calendar timeWithZone;
    private Calendar timeWithZoneLocal;
    @Id
    @SequenceGenerator(name = "seq", sequenceName = "TIMESTAMP_DEMO_SEQ")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
    public long getId() {
        return id;
    }
    @Column
    public String getName() {
        return name;
    }
    @Column(name = "TIME_WITH_ZONE")
    @Type(type = "com.swayam.demo.oracle.hibernate.custom.TimestampType2")
    public Calendar getTimeWithZone() {
        return timeWithZone;
    }
    @Column(name = "TIME_WITH_ZONE_LOCAL")
    @Type(type = "com.swayam.demo.oracle.hibernate.custom.TimestampType")
    public Calendar getTimeWithZoneLocal() {
        return timeWithZoneLocal;
    }
    public void setId(long id) {
        this.id = id;
    }
    public void setName(String name) {
        this.name = name;
    }
    public void setTimeWithZone(Calendar timeWithZone) {
        this.timeWithZone = timeWithZone;
    }
    public void setTimeWithZoneLocal(Calendar timeWithZoneLocal) {
        this.timeWithZoneLocal = timeWithZoneLocal;
    }
}

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.