Timezones in SQL DATE vs java.sql.Date

I'm getting a bit confused by the behaviour of the SQL DATE data type vs. that of java.sql.Date. Take the following statement, for example:

select cast(? as date)           -- in most databases
select cast(? as date) from dual -- in Oracle

Let's prepare and execute the statement with Java

PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, new java.sql.Date(0)); // GMT 1970-01-01 00:00:00
ResultSet rs = stmt.executeQuery();
rs.next();

// I live in Zurich, which is CET, not GMT. So the following prints -3600000, 
// which is CET 1970-01-01 00:00:00
// ... or   GMT 1969-12-31 23:00:00
System.out.println(rs.getDate(1).getTime());

In other words, the GMT timestamp I bind to the statement becomes the CET timestamp I get back. At what step is the timezone added and why?

Note:

• I have observed this to be true for any of these databases:

  DB2, Derby, H2, HSQLDB, Ingres, MySQL, Oracle, Postgres, SQL Server, Sybase ASE, Sybase SQL Anywhere

• I have observed this to be false for SQLite (which doesn't really have true DATE data types)
• All of this is irrelevant when using java.sql.Timestamp instead of java.sql.Date
• This is a similar question, which doesn't answer this question, however: java.util.Date vs java.sql.Date

Solution 1:

The JDBC specification does not define any details with regards to time zone. Nonetheless, most of us know the pains of having to deal with JDBC time zone discrepencies; just look at all the StackOverflow questions!

Ultimately, the handling of time zone for date/time database types boils down to the database server, the JDBC driver and everything in between. You're even at the mercy of JDBC driver bugs; PostgreSQL fixed a bug in version 8.3 where

Statement.getTime, .getDate, and .getTimestamp methods which are passed a Calendar object were rotating the timezone in the wrong direction.

When you create a new date using new Date(0) (let's assert you are using Oracle JavaSE java.sql.Date, your date is created

using the given milliseconds time value. If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

So, new Date(0) should be using GMT.

When you call ResultSet.getDate(int), you're executing a JDBC implementation. The JDBC specification does not dictate how a JDBC implementation should handle time zone details; so you're at the mercy of the implementation. Looking at the Oracle 11g oracle.sql.DATE JavaDoc, it doesn't seem Oracle DB stores time zone information, so it performs its own conversions to get the date into a java.sql.Date. I have no experience with Oracle DB, but I would guess the JDBC implementation is using the server's and your local JVM's time zone settings to do the conversion from oracle.sql.DATE to java.sql.Date.

You mention that multiple RDBMS implementations handle time zone correctly, with the exception of SQLite. Let's look at how H2 and SQLite work when you send date values to the JDBC driver and when you get date values from the JDBC driver.

The H2 JDBC driver PrepStmt.setDate(int, Date) uses ValueDate.get(Date), which calls DateTimeUtils.dateValueFromDate(long) which does a time zone conversion.

Using this SQLite JDBC driver, PrepStmt.setDate(int, Date) calls PrepStmt.setObject(int, Object) and does not do any time zone conversion.

The H2 JDBC driver JdbcResultSet.getDate(int) returns get(columnIndex).getDate(). get(int) returns an H2 Value for the specified column. Since the column type is DATE, H2 uses ValueDate. ValueDate.getDate() calls DateTimeUtils.convertDateValueToDate(long), which ultimately creates a java.sql.Date after a time zone conversion.

Using this SQLite JDBC driver, the RS.getDate(int) code is much simpler; it just returns a java.sql.Date using the long date value stored in the database.

So we see that the H2 JDBC driver is being smart about handling time zone conversions with dates while the SQLite JDBC driver is not (not to say this decision isn't smart, it might suit SQLite design decisions well). If you chase down the source for the other RDBMS JDBC drivers you mention, you will probably find that most are approaching date and time zone in a similar fashion as how H2 does.

Though the JDBC specifications do not detail time zone handling, it makes good sense that RDBMS and JDBC implementation designers took time zone into consideration and will handle it properly; especially if they want their products to be marketable in the global arena. These designers are pretty darn smart and I am not surprised that most of them get this right, even in the absence of a concrete specification.

I found this Microsoft SQL Server blog, Using time zone data in SQL Server 2008, which explains how time zone complicates things:

timezones are a complex area and each application will need to address how you are going to handle time zone data to make programs more user friendly.

Unfortunately, there is no current international standard authority for timezone names and values. Each system needs to use a system of their own choosing, and until there is an international standard, it is not feasible to try to have SQL Server provide one, and would ultimately cause more problems than it would solve.

Solution 2:

It is the jdbc driver that does the conversion. It needs to convert the Date object to a format acceptable by the db/wire format and when that format doesn't include a time zone, the tendency is to default to the local machine's time zone setting when interpreting the date. So, most likely scenario, given the list of drivers you specify, is that you set the date to GMT 1970-1-1 00:00:00 but the interpreted date when you set it to the statement was CET 1970-1-1 1:00:00. Since the date is only the date portion, you get 1970-1-1 (without a time zone) sent to the server and echoed back to you. When the driver gets the date back, and you access it as a date, it sees 1970-1-1 and interprets that again with the local time zone, i.e. CET 1970-1-1 00:00:00 or GMT 1969-12-31 23:00:00. Hence, you have "lost" an hour compared to the original date.

Solution 3:

Both java.util.Date and the Oracle/MySQL Date objects are simply representations of a point in time regardless of location. This means that it is very likely to be internally stored as the number of milli/nano seconds since the "epoch", GMT 1970-01-01 00:00:00.

When you read from the resultset, your call to "rs.getDate()" tells the resultset to take the internal data containing the point in time, and convert it to a java Date object. This date object is created on your local machine, so Java will choose your local timezone, which is CET for Zurich.

The difference you are seeing is a difference in representation, not a difference in time.

Solution 4:

The class java.sql.Date corresponds to SQL DATE, which does not store time or timezone information. The way this is accomplished is by 'normalizing' the date, like the javadoc puts it:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

This means that when you work in UTC+1 and ask the database for a DATE a compliant implementation does exactly what you've observed: return a java.sql.Date with a milliseconds value that corresponds to the date in question at 00:00:00 UTC+1 independently of how the data got to the database in the first place.

Database drivers may allow changing this behaviour through options if it's not what you want.

On the other hand, when you pass a java.sql.Date to the database, the driver will use the default time zone to separate the date and time components from the millisecond value. If you use 0 and you're in UTC+X, the date will be 1970-01-01 for X>=0 and 1969-12-31 for X<0.

Sidenote: It's odd to see that the documentation for the Date(long) constructor differs from the implementation. The javadoc says this:

If the given milliseconds value contains time information, the driver will set the time components to the time in the default time zone (the time zone of the Java virtual machine running the application) that corresponds to zero GMT.

However what is actually implemented in OpenJDK is this:

public Date(long date) {
    // If the millisecond date value contains time info, mask it out.
    super(date);

}

Apparently this "masking out" is not implemented. Just as well because the specified behaviour is not well specified, e.g. should 1970-01-01 00:00:00 GMT-6 = 1970-01-01 06:00:00 GMT be mapped to 1970-01-01 00:00:00 GMT = 1969-12-31 18:00:00 GMT-6, or to 1970-01-01 18:00:00 GMT-6?