Hello everyone.
I am trying to better understand timezone handling between Java Timestamps and a database timestamp columns.
Some databases may be different, but in my initial analysis, I am testing with DB2.
I started this discussion over on dbforums dot com -- look for the entry titled "DB2 Handling of DST" if you want to review it. (Not sure if it's acceptable policy to post a direct link to it, but it's pretty easy to find it for the time being)
Herein lies the problem. It would appear that the DB2 timestamp column holds no frame of reference. Java Timestamp objects DO hold a frame of reference. What I mean by this is that a Java Timestamp is a wrapper for Date, and Date is intended to reflect UTC. With a Java Timestamp, you can precisely identify an instant in time. However, in the database, the timestamp column does not appear to have any common frame of reference; in a manner of speaking, if you store '2015-11-1 01:55:00' in a database timestamp column, it seems it is almost literally storing '2015-11-1 01:55:00'. There does not appear to be any timezone, nor is it stored relative to any common timezone. One helpful person suggested that it's up to the application to provide this necessary frame of reference. The problem is, this seems to cause a whole mess of problems in Java.
For places with daylight savings time, the majority of the problems happen between 1am and 2am at the end of daylight savings time. In my prior example, I showed just such a date: '2015-11-1 01:55:00'. In the Central timezone for example, this time actually happens twice, once in daylight savings time (CDT), then again in standard time (CST). Whatever is happening in JDBC and DB2 makes trying to straight up store and retrieve a java.sql.Timestamp from the database to be unreliable. Here's a demonstration program.
public class DSTProblemDemo {
private static DateFormat dfLocal = new SimpleDateFormat("MM/dd/yyyy HH:mm z");
public static void main(String[] args) {
try {
demo();
} catch (Exception e) {
e.printStackTrace();
}
}
private static Connection getConnection() throws SQLException {
Properties props = new Properties();
props.put("user", "user");
props.put("password", "password");
return DriverManager.getConnection("jdbc:db2://server:port/dbname:currentSchema=WORKER;", props);
}
private static void demo() throws Exception {
Date date = dfLocal.parse("11/01/2015 01:55 CDT");
Timestamp timestamp = new Timestamp(date.getTime());
System.out.println("The timestamp is " + dfLocal.format(timestamp) + " (" + timestamp.getTime() + " ms)");
System.out.println("Inserting timestamp into the database...");
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement("insert into WORKER.DST_DEMO (TS) values ?");
ps.setTimestamp(1, timestamp);
ps.executeUpdate();
con.commit();
System.out.println("Querying timestamp from the database...");
Statement query = con.createStatement();
ResultSet rs = query.executeQuery("select TS from WORKER.DST_DEMO");
while (rs.next()) {
timestamp = rs.getTimestamp(1);
System.out.println("The timestamp is " + dfLocal.format(timestamp) + " (" + timestamp.getTime() + " ms)");
}
}
}
And here is what the output is:
The timestamp is 11/01/2015 01:55 CDT (1446360900000 ms)
Inserting timestamp into the database...
Querying timestamp from the database...
The timestamp is 11/01/2015 01:55 CST (1446364500000 ms)
I can't imagine anyone suggesting that this isn't a bad thing. You can get around this by using the statement methods that take a Calendar as an argument, and passing it a Calendar without daylight savings time like the GMT calendar. What you can do is create a Calendar, set it's timezone to GMT, then calling setTimestamp(1, timestamp, gmtCalendar) and getTimestamp(1, gmtCalendar), and this gets around the problem. I really don't like this though because every developer that touches this date needs to be informed that they need to, more or less, encode and decode the date in this way when working with it. Furthermore, some database tools allow you to display timezone for timestamp columns which further confuses everyone (if I'm right about how the database is storing the timestamp, then the database tool can't know the right timezone and is just guessing, and it will guess wrong when using such a technique). It gets even uglier if you are trying to use something like Hibernate or another ORM. How would you properly manipulate the timestamps in such a case?
What are some solutions to this problem? Ideally, we would like to use Timestamps as they exist in Java. They just work. But we need to know how to deal with them when operating with a database. I don't know why this topic doesn't come up often; it seems pretty important. I did do a search of the forums and didn't find any remotely recent posts on this topic.
Thanks for your thoughts,
Skotty