Friday, March 27, 2009

Avoid using JDK Date APIs to handle timezone sensitive date and time

by mssqlisv via SQL Programmability & API Development Team Blog on 3/27/2009 12:30:00 AM

JDK APIs for Class “java.util.Date” and “java.sql.Timestamp” (subclass of Date) including getHours(), getMinutes(), getSeconds(), getTimestamp() allow you to retrieve date/time related information. However, the JVM (Java Virtual Machine) won’t handle timezone sensitive data properly using these APIs. As matter of fact, these APIs were deprecated starting JDK 1.1 (http://java.sun.com/j2se/1.5.0/docs/api/java/util/Date.html). But I am still seeing ISV developers use these APIs in their JAVA applications, leading to incorrect results or behavior.

What happens is when date/time data is inserted into the SQL Server database, it’s stored correctly in SQL Server. However, when the date/time is read using the APIs mentioned above, the retrieved value is implicitly  converted to “local time” depending on where the host of JVM (Java Virtual Machine) is. For example, when a java application reads “1/8/2009 3:30:00 AM” from SQL Server database, you would get different results depending on the location of the app.

SQL Server (in pacific time zone):

create table datetime_tbl (id INTEGER unique not null, dateTime_v DATETIME not null)

insert into datetime_tbl (id, dateTime_v) values (1, '2009-01-08 03:30:00')

go

Java application:

Statement s = connection.createStatement();

query = "select id, dateTime from datetime_tbl where id = " + 1;

s.execute(query);

ResultSet rs = s.getResultSet();

rs.next();

String localApptimezone = Calendar.getInstance().getTimeZone().getID();

// -8 is offset of GMT to read the time as pacific time.

java.util.TimeZone timeZoneP = new java.util.SimpleTimeZone(-8*3600000, "GMT-8 (Pacific)");

java.util.Calendar cal= java.util.Calendar.getInstance(timeZoneP);

Timestamp tStamp = rs.getTimestamp(2, cal);

System.out.println("local application timezone: " + localApptimezone);

System.out.println("time: " + tStamp.toString() + " in " + timeZoneP.getID());

Results (note: 1 hour difference for Arizona test):

The recommended way of handling this type of scenario is to utilize DateFormat and avoid getTimestamp() and other aforementioned APIs. Alternatively, you can convert the date/time to character string directly.

Statement s = connection.createStatement();

query = "select id, dateTime from datetime_tbl where id = " + 1;

s.execute(query);

ResultSet rs = s.getResultSet();

rs.next();

String localApptimezone = Calendar.getInstance().getTimeZone().getID();

// -8 is offset of GMT to read the time as pacific time.

java.util.TimeZone timeZoneP = new java.util.SimpleTimeZone(-8*3600000, "GMT-8 (Pacific)");

java.util.Calendar cal= java.util.Calendar.getInstance(timeZoneP);

java.text.DateFormat dateFormat = java.text.DateFormat.getInstance();

dateFormat.setTimeZone(timeZoneP);

System.out.println("local application timezone: " + localApptimezone);

System.out.println("time: " + dateFormat.format(cal.getTime()) + " in " + timeZoneP.getID());

Results (note: consistent results for both region tests as expected):