MySQL Bugs: #3369: getDate() in Result Set returns incorrect Date

Bug #3369	getDate() in Result Set returns incorrect Date
Submitted:	2 Apr 2004 9:09	Modified:	28 Mar 2014 14:02
Reporter:	Carlo Germana	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.0.11	OS:	Windows (WinXP)
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
Hi,

Since Version 3.0.11 of the Connector/J driver we noticed that a date retrieved from a resultset using getDate() return incorrect data.
If the date on db looks like 2004-04-03 the date returned by the driver is: 2004-04-02. Replacing the driver with Version 3.0.8 everything is ok.

I look forward hearing from you soon.
Kind regards,
Carlo Germanà 

How to repeat:
Retrieve a date field from a jdbc connection:

Ex.
Date myDate = res.getDate();

I couldn't repeat this myself with 3.0.11 or the latest version of 3.0.x.

You might consider trying a nightly snapshot build of 3.0.x to see if this corrects your issue. See http://downloads.mysql.com/snapshots.php

Hi
We use mysql 4.0.18-max-nt together with Oracle SQLJ Release 9.2.0.1.0.
Using only the a simple mysql jdbc connection the error does not occur with any driver version.
So I will try to reproduce this error case with a simple sqlj source code snippet and Version 3.0.11 of the mysql jdbc driver.

Kind regards,
Carlo

Running mysql with a veriety of jdbc driver builds, same problem occurs 
Date returned from getDate is always one day behind that held in the database.

Solution was basically to move the whole installation from NT to XP in this case.

Problem gone.

Correction to previous entry, the problem appears when the server is running in a
timezone ahead of the client application.

Insert date field into MYSQL database eg '19-11-1969'
 
What is actually held in the database is a long equating to '19-11-1969 00:00:00.0' 
 
Assuming the Server is one hour ahead of the client, getDate takes off an hour 
'18-11-1969  23:00:00.0' giving a get date result of '18-11-1969' . One day lost.

To correct I have basically forced all client application to use the same time zone
with java.util.TimeZone.setDefault(tz)

I've started with the same symptom: a date written in a DATE column came back a day before when read.

I've looked into the code and posted another comment on bug #23584, which I believe can be related with this problem.  Please read my comment there.

To summarize, the implementation of ResultSet.getdate(int, Calendar) is not respecting the ResultSet API description, because it eventually leaves out the timezone information, causing the reading of the DATE field from the database to be done in the default timezone (has coded in TimeUtil.fastDateCreate() when Calendar is null).

I'm in Portugal, currently with daylight savings and I wrote today's date to the database in UTC TZ, which correctly appear in the DB.  When reading it, I got the day before because it came out in my TZ (which happens to be one hour behind).  If I execute the program after October 26 (after daylight savings ends) the bug will not be visible (by sheer bad luck, of me being the same TZ as UTC).

I close this report as "Can't repeat" because there is no feedback for a long time and codebase is too old. There are several related bug reports against latest 5.1 driver, eg Bug#71084, please refer them in case of problems.