Can anyone explain the usecase of jdbc resultset getTimestamp(int,calendar)?

Question

I'm talking about this interface method:

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp%28int,%20java.util.Calendar%29

The most commonly used implementation being the one in cachedrowset:

http://hg.openjdk.java.net/jdk8/jdk8/jdk/file/687fd7c7986d/src/share/classes/com/sun/rowset/CachedRowSetImpl.java line 6170

You will notice that the implementation does two very weird things:

1) it modifies the calendar passed as argument, even though there is also a return value

2) it extracts all the time information from SQL, except for the milliseconds, which come from the calendar passed as argument.

The interface description is rather unclear, but assuming the implementation is correct - What is the point of this method? I can understand a method that would take a calendar to extract just the timezone, without modifying it. But taking a calendar, modifying it, and extracting not only the zone but also the milliseconds...

Does anyone have any insight as to the history/design/reasoning behind this API?

Answer 1

SQL databases store the date and time value for timestamps in the form of year, month, day, hour min, sec etc. The instance in time these values specify depends on the timezone in which they are interpreted (e.g. 2014. january 1. 15:00:00 is not at the same time in Europe than in the USA). The timezone may or may not be part of the timestamp, depending on the column's type.

The Java java.sql.Timestamp and java.util.Date classes represent an instance of time regardless of the time zone (or rather in a fixed, UTC timezone).

If the column in the SQL database does not store the timezone info along with the date+time, in order to create a Java Date or TimeStamp object from such a timestamp requires a timezone so it can point to a specific instance in time (in the reference UTC timezone).

The ResultSet.getTimestamp() method in question can be used to get an SQL timestamp data and convert it to a Java Timestamp instance using the timezone info set in the parameter Calendar object.

Answer 2

This seems to be an incorrect interpretation of the JDBC API documentation (and unfortunately the javax.sql.rowset has more of those incorrect interpretations).

By default a JDBC driver needs to store or retrieve a time or timestamp as if the time stored in the database is in the current timezone of the JVM. As this isn't always what you want, the API provides methods to provide a Calendar object, which you need to use to derive the actual timezone to use (I don't know why they didn't use java.util.TimeZone instead).

This is specified in PreparedStatement.setTimeStamp:

With a Calendar object, the driver can calculate the timestamp taking into account a custom timezone. If no Calendar object is specified, the driver uses the default timezone, which is that of the virtual machine running the application.

The method getTimestamp in ResultSet follows (or should follow) these same rules.

So if a database stores time '11:20' and your local timezone is CET (UTC+1), then the retrieved time is 10:20 UTC (11:20 CET). However if I provide a Calendar in GMT, the returned time should be 11:20 UTC (12:20 CET).