Timezones in SQL DATE vs java.sql.Date

Question

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

question from:https://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date

Answer 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.