MySQL Bugs: #64798: setTimestamp - Add option to use passed time zone in formatter

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:	None
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

Description:
Background:

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:
Example:
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:
  this.tsdf.setTimeZone(tz)
2.Like (1) but only do this when a parameter is specified like "useTimezoneForFormatter=true":
  if (useTimezoneForFormatter)
      this.tsdf.setTimeZone(tz)

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)
     this.tsft.setTimeZone(formatterTimezone)
}

2. use multiple formatters. either recreate it each time or save them in a map by passed in timezone.

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.

any news?
I can be reached at samz@cellebrite.com for more details.

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.

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

==UPDATE==
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.

Yes, I mean that you are using useLegacyDatetimeCode=false. Sorry for didn't mention that.

The useLegacyDateTimeCode connection property takes care of this bug.