Retrieving Date/Time/Timestamp objects using JDBC

  • [アーティクル]

   Finally, we can talk about how Microsoft SQL Server 2005 JDBC driver handles Calendar objects and temporal values. Data retrieval seems like a good enough point to start. You can use ResultSet and CallableStatement getDate/getTime/getTimestamp methods to retrieve java.sql.Date, java.sql.Time and java.sql.Timestamp values from your database. There are overloaded versions of these methods where you can specify a Calendar object that should be considered during retrieval. How our JDBC driver uses this Calendar object will be the main topic of this post.

Let's start with an example. It's almost the middle of August and another rainy day embellishes the notorious Seattle summer. I think I deserve to pick a more exotic place to base my examples on:-) Suppose I'm not awfully happy about Seattle weather and I'm looking for discount tickets to a sunnier place. America/Costa_Rica will do. So, online I find this amazing one-way flight from Seattle to Costa Rica. The flight company is based in Costa Rica and hence stores all flight times in its database in its local timezone. However, when I query their website for flight information they have to provide me with Seattle time for departure. They are storing their data in SQL Server 2005 and they use our JDBC driver to operate on their server. The departure time is stored in a datetime column. Here is how they would get the right information for me from their database using ResultSet.

  Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("America/Costa_Rica"));
ResultSet rs = stmt.executeQuery("Select * from flights");
rs.next();
System.out.println("Departure time(String) : " + rs.getString("departureTime"));
System.out.println("Departure time(Timestamp) : " + rs.getTimestamp("departureTime", cal));

When we retrieve the value of departureTime column as a String we are receiving the data stored in the server formatted in our timezone. This data is not what we are looking for. For one, we know the data is stored in Costa Rican time. For another, with this approach we cannot even be sure if we are getting the departure time reported in Costa Rican timezone due to formatting in our local timezone. What we really need to do here is to provide a Calendar argument that specifies which timezone the data was stored in. Then, our driver will retrieve the data from the database and convert it to my local timezone, PST. There is currently an hour difference between Costa Rica and Seattle and the departure time for me should be an hour earlier than what is actually stored in the database. Here are the results.

  Departure time(String) : 2008-08-10 21:28:08.963
Departure time(Timestamp) : 2008-08-10 20:28:08.963 //departure time in my timezone, PST

If I hurry, I can still make it to the airport on time. However, I would feel bad if I did not touch upon normalization issues with Date and Time. Quickly then. What do you think would be the results of calling getDate and getTime on this column with the same Calendar argument?

  System.out.println("Departure time(Date) : " + rs.getDate("departureTime", cal)); //prints 2008-08-09
System.out.println("Departure time(Time) : " + rs.getTime("departureTime", cal)); //prints 19:28:08

According to getDate result, I already missed this plane! Surprising, isn't it? Well, not if you heard about normalization. Normalization is essentially setting the unused components of Date and Time objects to the epoch in UTC, 1/1/1970 00:00:00.0.  JDBC 3.0 spec holds the JDBC driver responsible for normalizing the values returned by getDate/getTime. In a recent QFE we changed our interpretation of when normalization should take place. Now, our driver performs normalization in the provided timezone as opposed to the application's local timezone. So, how exactly do we end up with a date that's a day earlier? Well, if you load up the milliseconds stored in the server in a Calendar with the given timezone, i.e. that of Costa Rica and then set the Hour, Minute, Second and Millisecond fields of Calendar to 0 as in the epoch, you will receive a date that is a day earlier. Hence, what we are converting to date here is no longer "2008-08-10 21:28:08.963" but it is "2008-08-10 00:00:00.000".  You can simulate this result with the following code.

  cal.clear();
cal.setTimeInMillis(rs.getTimestamp("departureTime");
cal.set(Calendar.HOUR_OF_DAY, 0);
cal.set(Calendar.MINUTE, 0);
cal.set(Calendar.SECOND, 0);
cal.set(Calendar.MILLISECOND, 0);
System.out.println("Converted date : " + new java.sql.Date(cal.getTimeInMillis()));

Similarly, for getTime the normalization is again implemented in the supplied Costa Rican timezone and you are actually converting "1970-01-01 21:28:08.963" to Time value. It just so happens that on this date the time difference between Seattle and Costa Rica is 2 hours instead of 1 because Seattle observes DST while Costa Rica doesn't. Not bad is it? Off I go now. If you like to daydream about sunnier places in the world, and want to know what time it is somewhere else, you can always visit the website below. It's a treasure if you are working on timezones.

https://www.timeanddate.com/

Comments