Monday, August 16, 2010

Daylight Saving Time (DST) and Timezone Handling in Java, JDBC and Oracle

The following problem has been puzzling me for 2 days.
Because MISO (Midwest ISO. A power market for several Mid West regions) doesn't support DST, it sent us data at hour 2 on Mar 14,2010 which is the DST beginning date (the hour 2 is supposed to be skipped if the ISO supports DST).

The date values in question are stored in an Oracle column called endDate which is of Oracle's date type without time zone information. In other words, you interpret such a date type's components (year,month,day,hour,minute,second and millisecond) in your local timezone.
If your data are across timezone, you should use Oracle's "timestamp with timezone" or "timestamp with local timezone". Such a date type has an additional timezone component based on which you interpret other components.

This additional timezone component is the key to understand the difference between Oracle's date type and Java's date type.
Because Java's date type represents the specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT. (Don't miss this GMT timezone!)
When you try to load an Oracle date value without a timezone into a Java date object, you will face difficulty without using any timezone in Java.

We used Hibernate's TimestampType to map this DB column to a Java Date type.
I used the following code to extract the hour in the endDate:
    Calendar cal = Calendar.getInstance();    //(1)
  int he;                                   //(2)
       
  cal.setTime(getEndDate());                //(3)
  he = cal.get(Calendar.HOUR_OF_DAY);       //(4)
The code is running in east coast which is currently in DST (it is August 2010). The hour in line (4) returns 1 for the hour 2 in question and the getEndDate()'s toString() also shows hour 1 instead of hour 2 or hour 3.

Getting to know why this happened is quite involving and confusing mainly due to the DST switch. We explain it in three steps.

First we need to know how Hibernate and JDBC driver retrieves you endDate value to a Java date object.
Hibernate's TimestampType just calls the following ResultSet's method to get the endDate.
   java.sql.Timestamp getTimestamp(String columnLabel);  
Because our DB column doesn't have timezone, what timezone will be used in the returned Timestamp (it extends date type) in the above method (still remember my previous statement "Java's date type represents the specified number of milliseconds since the standard base time known as "the epoch", namely January 1, 1970, 00:00:00 GMT. ")?
Astute readers may recall there is any similar method in ResultSet that allows you to provide a timezone through a Calendar:
   java.sql.Timestamp getTimestamp(int columnIndex, Calendar cal) ;
This method is exactly to handle a DB column without a timezone so that you can interpret the DB date times in a specified timezone based on your business logic.

The Java document for the first method doesn't say what time zone is associated with the returned timestamp value. This is unfortunate and different JDBC drivers may do different things. Later I will present what I found based on my testings.
Actually Java's date doesn't care about any time zone per se; it only remembers the the number of milliseconds since the "epoch". It is Java's calendar that incorporates a timezone (and also a locale).
Remember the preferred way to create a date object is to create a calendar first then call its getTime() to return the date. Calendar's getTime() creates a date by calculating the number of milliseconds since the "epoch".
On the other hand, you can assign a date value to a calendar whose time zone may be different from the original time zone that created the date.
For example, you created hour 1 in EST(GMT-5), then you assign this time to a calendar whose time zone is CST(GMT-6). The assigned calendar will returns a date whose hour is 0.
We can also infer that in order to return the same hour, the 2 time zones assigned to the 2 calendars must be the same.

Secondly, Java has different Calendar creation call syntax.
They behave differently for different timezones on Mar 14,2010(or any other DST beginning dates) even they all refer to the same region.
Take the New York region for example. Suppose our code runs in New York, the following 3 calls all return time zones in the New York region:
  Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT-5:00")); //EST. No DST support      (1)
  Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT-4:00")); //EDT. No DST support      (2)
  Calendar cal = Calendar.getInstance(); //default to "America/New_York". Supports DST  (3)

With (1), you can set the calendar's hour to 1,2 and 3 etc, even hour 2 doesn't exist and it actually corresponds to hour 3 in EDT.
With (2), you can also set the calendar's hour to 1,2 and 3 etc, even neither hour 1 or 2 exists and they actually correspond to hour 0 and 1 in EST, respectively.
With (3), you can set the calender to all 24 hours except 2. Specifically hour 1 corresponds to hour 1 in EST and hour 3 and later correspond to hours in EDT. When you try to set hour 2, Java actually changes to hour 3 in EDT because call syntax (3) supports DST and hour 2 doesn't exist.
(It is easy to understand if you just think the time zones in call syntax (1) and (2) are some first-class time zones and the time zone in (3) can be either GMT-5 or GMT-4 depending on the hour).

Finally we connect the dots together and shows you why line (4) got hour 1. 
When Hibernate uses the getTimestamp() without a calendar, the Oracle JDBC driver uses GMT-5 for hour 1 and GMT-4 for other hours to create a calendar and eventually returns a timestamp (I am not sure whether this is standard practice). So the hour 2 in the endDate is kept as hour 2 which actually corresponds to hour 1 in EST.
Because Line (1) is the Calendar call syntax (3) which supports DST, line (4) returns the actually hour 1 in EST.

Actually our application needs to return whatever hour MISO sent us without any DST offsetting. Based on the above analysis of Date and Calendar, line (1) must specify the same timezone as the one used to create the endDate by the JDBC driver. Unfortunately this is a guess game if you DB column doesn't have timezone information.

There are 2 solutions.
One is to design a DB column with time zone information.
The other is to extend Hibernate's TimestampType by explicitly specifying a GMT timezone such as your local timezone based on the raw GMT offset (Such Timezones ignore DST schedules).

Lastly the toString() from a Date object is based on your local timezone which may confuse you when your intended timezone is different.

4 comments:

  1. Encountered the exact problem today on DST switch.

    ReplyDelete
  2. We had hours falling behind by an hour which rectified itself as we suspect at 3:00AM without any change in code / configuration. Need to look for a better solution. May be keeping dates as String will save the hassle of converting oracle date to java date specially on DST switches.

    ReplyDelete
  3. yes my solution of using a specific DST time zone instead of the default has a big drawback.
    Since the most of other code in your project probably still use the default time zone, my solution doesn't interface well with the rest of code. So in my case I had to convert date back and forth when I interfaces with other code.

    ReplyDelete