Bug #64798 setTimestamp - Add option to use passed time zone in formatter
Submitted: 29 Mar 2012 10:52 Modified: 16 Jul 2012 12:41
Reporter: sam zilverberg Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S4 (Feature request)
Version:5.1.13 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: GMT, jdbc, timestamp, timezone, UTC

[29 Mar 2012 10:52] sam zilverberg

App is in one timezone.
mysql is in another timezone.
Neither are gmt.
We want to save java date/timestamp in UTC timezone in a datetime column.
We don't (or can't) mess with mysql/server/app timezone settings.
Instead we want to pass the date/timestamp object with the wanted timezone and expect it to be saved in that timezone format in the db.

The problem:

mysql jdbc driver uses a SimpleDateFormat to format the given object and this formatter uses the default vm timezone.
So in dst transitions the jdbc driver can skip/go back an hour and save a wrong date/time.

How to repeat:
App is running in jvm with timezone of America/Chicago.
The app wants to save the date/time "2012-03-10 02:00:00" - a valid utc time, but not a valid chicago time because a transition occurs and it is actually 03:00.
A SimpleDateFormat with the default(chicago) time zone can't format any date to "2012-03-10 02:00:00", it automatically skips to 03:00.
There is no way to "fool" it into printing such a date, even when using parameters useTimezone and serverTimezone...

Related code from setTimestampInternal:

if (this.tsdf == null) 
    this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''");

timestampString = this.tsdf.format(x);
setInternal(parameterIndex, timestampString)

Suggested fix:
One of the following:
1.Either use the passed timezone in the formatter before formatting the object:
2.Like (1) but only do this when a parameter is specified like "useTimezoneForFormatter=true":
  if (useTimezoneForFormatter)
[29 Mar 2012 11:00] sam zilverberg
Add on to suggested fix:
The jdbc driver uses "this.tsdf" - a single SimpleDateFormat.
The suggested fix sets this formatter timezone.
This might affect other methods using this formatter.

To avoid problems either:
1. use fix (2) and set the formatter timezone only once according to another parameter : "formatterTimezone='UTC'" and code :
if (this.tsdf==null){
  this.tsdf = new SimpleDateTimeFormatter();
  if (useTimezoneForFormatter && formatterTimezone != null)

2. use multiple formatters. either recreate it each time or save them in a map by passed in timezone.
[29 Mar 2012 12:44] sam zilverberg
I put the mysql version by mistake instead of the connector version.
The connector is not the latest version but looking at changes logs shows this problem wasn't fixed since.
[1 Jul 2012 13:35] sam zilverberg
any news?
I can be reached at samz@cellebrite.com for more details.
[2 Jul 2012 10:13] Alexander Soklakov
Hello Sam,

You can try setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal) method where Calendar object has required timezone.

The problem still exists with default calendar, not with given explicitly.

Please report, if this doesn't help.
[2 Jul 2012 13:43] sam zilverberg
Passing in the calendar doesn't resolve the problem because the problem lies in how this calendar is used.

Recap of the "how to repeat" example I gave.:
The passed in calender is used in TimeUtil.changeTimezone to calculate the millis instance for a new date object that will be represent the passed in time - but locally. Then this date object is formatted to string using a SimpleDateFormatter with the default timezone.

So if a date object 2012-03-10 02:00:00 GMT is passed in, the time util changes recalculates the millis to create a date object 2012-03-10 02:00:00 America/Chicago. At this point an error is thrown because the date/time in that time zone does not exist because of a time transition.
In any case, even if it did not throw an error, the formatter with the default (america/chicago) tz would not be able to output the given date as it should be. It would consider the transition and jump one hour forward...

I just noticed there's a useLegacyDatetimeCode connection property that I can set to false and then instead of the old logic in setTimestampInternal a different method - newSetTimestampInternal - is called. In this method the formatter is configured to be in the passed in time zone so it should work as expected... I'll try this method.
[2 Jul 2012 14:48] Alexander Soklakov
Yes, I mean that you are using useLegacyDatetimeCode=false. Sorry for didn't mention that.
[16 Jul 2012 12:41] sam zilverberg
The useLegacyDateTimeCode connection property takes care of this bug.