Timezones in SQL DATE vs java.sql.Date
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.
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.
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.