For storing the TimeZone information in TimeStamp, Oracle (10g) has two options:
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.
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.
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?
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).
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.
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.
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.
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.
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.
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.
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.
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.