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

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 (https://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):

 

Location of app/JVM	Result
Washington (Pacific time zone)	local application timezone: America/Los_Angeles time: 2009-01-08 03:30:00.0 in GMT-8 (Pacific)
Arizona (Mountain time zone)	local application timezone: America/Phoenix time: 2009-01-08 04:30:00.0 in GMT-8 (Pacific)

 

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):

 

Location of app/JVM	Result
Washington (Pacific time zone)	local application timezone: America/Los_Angeles time: 1/8/09 3:30 AM in GMT-8 (Pacific)
Arizona (Mountain time zone)	local application timezone: America/Phoenix time: 1/8/09 3:30 AM in GMT-8 (Pacific)

 

Cross Posted from https://blogs.microsoft.com/mssqlisv

Comments

• Anonymous
  March 26, 2009
  Heh, .NET's DateTime XML serialization did the same thing at first. Maybe this is where they got the idea from. Beats me why everyone doesn't just use UTC :.

• Anonymous
  October 28, 2010
  This sounds like a sqljdbc problem, right?  The driver converts SQL values from the database to java.util.Date (etc) types, right?  Rather than do it incorrectly, sqljdbc could just as easily throw an exception ("not castable to java.util.Date.  Do not use a deprecated API, silly"). Another solution would be to have the driver handle java.util.Date correctly (putting it in as a UTC or whatever is necessary to get the desired/expected behavior). I'm interested in hearing what others think.

• Anonymous
  October 28, 2010
  This sounds like a sqljdbc problem, right?  The driver converts SQL values from the database to java.util.Date (etc) types, right?  Rather than do it incorrectly, sqljdbc could just as easily throw an exception ("not castable to java.util.Date.  Do not use a deprecated API, silly"). Another solution would be to have the driver handle java.util.Date correctly (putting it in as a UTC or whatever is necessary to get the desired/expected behavior). I'm interested in hearing what others think.