Bug #15604 TimeZone discarded storing java.util.Calendar into DATETIME
Submitted: 8 Dec 2005 21:15 Modified: 16 Jul 2009 12:54
Reporter: Stuart Maclean Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.12 OS:Linux (Linux RHEL3)
Assigned to: Mark Matthews CPU Architecture:Any

[8 Dec 2005 21:15] Stuart Maclean
Description:
This only applies to PreparedStatements in the mysql driver.  Statements work OK.

Have a table with a column of sql type DATETIME  (a type which has no timezone association)

In java app, have a java.util.Calendar cal with UTC (same as GMT) set for timezone (my VM timezone is America/Los-Angeles). To store into DATETIME, only available jdbc method is

Timestamp t = new Timestamp( cal.getTimeInMillis() );
ps.setTimestamp( N, t, cal );

This fails to store the desired DATETIME, since the timezone held by the calendar is discarded.  I browsed the sources for 3.1.12 and found this:

1 if use PreparedStatement, error is in PreparedStatement.setTimestampInternal, where the SimpleDateFormat fails to set its timezone to match the tz parameter:

			// need this before the format call
			this.tsdf.setTimeZone( tz );
			// end 

			timestampString = this.tsdf.format(x);

A SimpleDateFormat will by default pick up the VM timezone.

2 if using ServerPreparedStatement, two parts of error:

a) in ServerPreparedStatement.setTimestampInternal, fails to store the tz in the BindValue,
only stores the TimeStamp object.  This java type has no associated
timezone.  The call to TimeUtils.changeTimeZone may alter the input tz
but does not, and cannot given its return type of Timestamp, alter the
fact that no timezone info is stored in the BindValue.

b) in storeDateTime413AndNewer (same issue with 412AndOlder), the
Calendar object used to extract parts of the Date parameter dt (where dt
resolves to the Timestamp object stored in the earlier BindValue), is built in
the local timezone of the current VM:

		if (this.dateTimeBindingCal == null) {
			this.dateTimeBindingCal = Calendar.getInstance();
		}

So although the timestamp object maintained does contain the user-intended
millis since the epoch value, the user intended timezone is NOT
carried forward in the executeUpdate process.

There may be other locations in the code where the same issue arises, I'm not sure how many implementations of java.sql.PreparedStatement there are in the driver

Hope this helps

Stuart Maclean

How to repeat:
1 In Java app, create a java.util.Calendar object. 

2 Set its timezone to something other than your local VM timezone.

3 Store this via preparedStatement.setTimestamp(int,Timestamp,Calendar), executeUpdate() sequence. 

4 Incorrect DATETIME (string representation?) will be sent to the db 

Suggested fix:
ensure timezone info is maintained, and always use that timezone in any calendar or simpledateformat usage
[8 Dec 2005 21:35] Mark Matthews
Can you tell us what timezone (and resultant timestamp value) you expect to see stored in the database?

The only reason I ask is bugs similar to this have been numerous, and it all comes down as to how one interprets the JDBC specification, the SQL standard and how it relates to what MySQL can do since it doesn't store timezone information with DATETIME values.

We've tested these various bugs with other vendors, and they all do different things too, so I need to know what you _expect_ to see stored (and retrieved) to be able to determine if this is already addressed by a configuration parameter we have.
[8 Dec 2005 22:33] Stuart Maclean
Expect this behaviour:

With a java calendar object whose timezone is UTC and whose value is say 20051201000000, i.e. midnight on Dec 1 2005 in the UTC timezone, I expect to see this shown as

 2005-12-01 00:00:00

when stored and retrieved from mysql, when its server time zone is set to UTC ('show variables' shows time_zone as UTC).

What I actual see is this

2005-11-30 16:00:00

The timezone under which my Java app ran which did the inserts is Pacific Standard Time, 8 hrs behind UTC.
[8 Dec 2005 22:37] Mark Matthews
Do you use "useTimezone=true" in your JDBC URL? Timezone conversion between client and server isn't done unless that value is used.
[8 Dec 2005 22:49] Stuart Maclean
I have tried both with and without the useTimezone=true option.  Why would it make a difference though, if my client  java.util.Calendar object has a UTC tz and the mysql server is also set to UTC tz?

I assume that the correct url you referred to would be

jdbc:mysql:///mvt?useTimezone=true
[8 Dec 2005 22:58] Stuart Maclean
I don't understand the reason for the call to TimeUtil.changeTimeZone involved in the flow control I mentioned initially.  A java Timestamp object is a point in time, i.e. has a set value for millis since epoch.  That value is independent of any time zone.    A timezone just affects how people refer to that point in time, ie '10am' in London vs '5am' in NY. 

So why a call to possibly change the timestamp's value  (TimeUtils.changeTimeZone, which actually returns a new Timestamp with a  possibly different value to the original)
[2 Feb 2006 18:57] Mark Matthews
We have to change the timezone of it to store it in the MySQL server if you've enabled timezones, as the MySQL server always stores and reports TIMESTAMPs in the timezone of the server.

Since the JDBC driver stores timestamps (in most cases) without converting them to strings first, they have to be adjusted to the server timezone before sending them to the server, because as-is, because touching any of the components of the timestamp, either via deprecated methods, or via putting into a java.util.Calendar use the timezone that the timestamp was created with.

Unfortunately, MySQL doesn't allow a timestamp to be sent from a client as millis since epoch, then this would be easy.
[3 Feb 2006 22:11] James Berry
I just encountered this bug also (at least the part of this bug having to do with ServerPreparedStatement).

I want to store GMT dates on the server, and have set the server to use the GMT timezone. So it should be clear the my database server is effectively in a different timezone than my java application.

The problem with ServerPreparedStatement, as described also by the original poster, is that timestamps are always converted into the local java vm timezone due to the fact that Calendar.getInstance() is called to create the calendar with no timezone (and thus the timezone used to create the timestamp is always that of the local java vm).

Note that in my case this leads to a problem where dates cannot be round-tripped between the server. I've got useTimezone and useGmtMillisForDatetimes both set to true (though I'm not sure I need them both). But in any event, timestamps that are stored on the server as gmt (verified through the command line), are successfully retreived as gmt. But when those same timestamps are written through ServerPreparedStatement, they are written for the local java vm timezone.

I'll note that this bug is not present in the Connector/J 5.0 beta, at least as long as useJDBCCompliantTimezoneShift is set to true. With that setting, the calendar used to create the timestamp is always the UTC calendar, and dates stored on the server in GMT can be successfully round-tripped.

So basically, I argue that Connector/J 3.1.12 is broken for any case except when the database timezone is the same as the local java vm timezone, because no matter what else you do, the connector will always offset the ultimate data into the local timezone, due the use in ServerPreparedStatement of the local vm timezone. I'll also argue, with a bit less conviction, that since the date has already been offset at that point to compensate for the server timezone, that it would be right in this case to always use a UTC calendar to create the timestamp in this routine.
[3 Feb 2006 22:16] James Berry
I want to point out that the comment [2 Feb 19:57] by Mark Matthews completely ignores the problem where the timestamp is always written into the local java vm timezone by  ServerPreparedStatement. Here's the code:

	private void storeDateTime413AndNewer(Buffer intoBuf, java.util.Date dt)
			throws SQLException {
		// This is synchronized on the connection by callers, so it is
		// safe to lazily-instantiate this...
		if (this.dateTimeBindingCal == null) {
			this.dateTimeBindingCal = Calendar.getInstance();
		}

The calendar so created is set to the local vm timezone, thus timestamps are always converted into local time, no matter what other timezone shifts have been done earlier. This is a problem!
[7 Apr 2006 14:28] Paul Christmann
I just encountered a slightly different variation on the same problem.  I'm trying to use a DATE column.  My code is roughly like this:
    Calendar cal = Calendar.getInstance("GMT");
    cal.set(Calendar.YEAR, year);
    cal.set(Calendar.MONTH, month);
    cal.set(Calendar.DAY_OF_MONTH, day);
    
    java.sql.Date sqlDate = new java.sql.Date(cal.getTime().getTime());

    preparedStatement.setDate(index, sqlDate, cal);

My sqlDate has the value I want (for example, 00:00:00 April 15th 2006 UTC), but the value that gets inserted into the database is one day off: April 14th, 2006.  The reason seems to be that the Calendar I'm providing to the setDate method isn't used.  The PreparedStatement code is:

	public void setDate(int parameterIndex, java.sql.Date x, Calendar cal)
			throws SQLException {
		setDate(parameterIndex, x);
	}

The Calendar variable is just discarded, and the setDate method that gets called converts the provided Date using the my local time zone.

(This is an entirely standalone app, so I'm not synchronizing time zones between a client and a server.  But I am replicating the database to other locations).
[19 Jun 2006 17:56] Ashley Martens
What I don't understand is why we have to use a special flag to tell Connector/J not to violate the basic principal of the java.util.Date, and thereby the Timestamp, object.
[19 Jun 2006 18:06] Mark Matthews
Ashley (good to hear from you again after the Users' Conference).

If you're referring to the "useTimezone=true" flag, it's because the TIMESTAMP type in MySQL does not have a timezone component, so the driver assumes you actually _want_ timestamps stored without adjusting for differing timezones (and thus changing the epoch). Some users want this, because they have clients in different timezones, and a server in yet another timezone, and the only way to support the same _epoch_ based time is to convert from one zone to another before storing.

Timestamps in Java are troublesome, mostly because they reused java.util.Date, which unfortunately doesn't match up with the SQL notion of 
a TIMESTAMP which has _no_ timezone information, and isn't even GMT (yes, it really says that in the SQL standard), so you basically have an impedence mismatch, and various RDBMS vendors treat this differently, which is why we have different flags to configure the behavior a user wants, because there isn't yet one "right" answer.

If you could give a simple example of what you expect to happen in your scenario, I'll dig into this a little further to see if we support it, or if this is yet another corner case that we need to consider.
[19 Jun 2006 20:08] Ashley Martens
Hey Mark,

I tried the setting out and it solved the problem for MySQL but it seems that MS SQL has the same issue so I'll have to find a different way to fix my situation.

The problem is this. I have a client in PST, a client in EST and the server in GMT; for example. And I'm keeping a log of the changes to the database. This log has a timestamp, which is set by the client (stupid I know). Either of the clients can list the log through our client and the timestamp should be formatted for the local timezone of the client. My first thought is to just store the Date.getTime() value in the column and be done with it.
[10 Oct 2006 11:31] Lapo Luchini
The problem is that whatever options are used the final "date-to-string" call uses always:
new java.text.SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", java.util.Locale.US);

All "useGmtMillisForDatetimes=yes", "useTimezone=yes" or even "useJDBCCompliantTimezoneShift=yes" do is change the Date object before it is printed, but the final formatting is always delegated to the very same local-time SimpleDateFormat (it uses local time because no TimeZone is set to it, and local time is the default).

What is the problem using local time? DST.
Local time, using DST in most countries, is NOT MONOTONOUS: it "lacks" one full hour when it takes affect and has a "double" hour when it goes out.

As an example, in my local TimeZone (Europe/Rome) I've got:
new java.util.Date(1143334799000L).toString() = "Sun Mar 26 01:59:59 CET 2006"
new java.util.Date(1143334800000L).toString() = "Sun Mar 26 03:00:00 CEST 2006"
new java.util.Date(1162083599000L).toString() = "Sun Oct 29 02:59:59 CEST 2006"
new java.util.Date(1162083600000L).toString() = "Sun Oct 29 02:00:00 CET 2006"

Whatever the function changeTimezone() does with the Date object (adding or subtracting arbitrary and pseudo-random amounts of milliseconds) it CAN NOT change the fact that the Date is formatted using local time zone, that:
a. is not necessarily the same as the server's
b. is not monotonous and some dates it simply can't POSSIBLY represent, or can represent with two different values

IMHO that just isn't right... IMHO a simple addiction would render it "better":
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("''yyyy-MM-dd HH:mm:ss''", java.util.Locale.US);
sdf.setTimeZone(server_time_zone);

Using the internal timezone managing would solve two issues:
a. mostly remove the need for the "millisecond fiddling" in changeTimezone()
b. use the actual timezone used by the server, and not one that is "with the same offset but with different non-linearities" (assing and subtracting milliseconds to the raw value can move the "missing hour" and the "double hour" around, but it will NEVER "solve" the problem they do exist in the local timezone, even if the server @@global.time_zone='GMT' and every SQL call is made using a GMT Calendar)
[11 Oct 2006 10:29] Alex Dupre
I came up with a patch to the connector to fix this and related bugs.

The patch does the following:
- always use timezone conversion on datetime/timestamp if the server supports timezones (otherwise you have inconsistent data)
- use timezone conversion on datetime/timestamp if the server doesn't support timezone, but you have set the serverTimezone variable
- use the JDBC calendar in get/setTimestamp to do timezone conversion if the server doesn't support timezones and you have not set the serverTimezone variable
- don't do any conversion on date and time (never, you can't, there isn't a timezone associated to such values!)

More important, the patch does the *corret* timezone conversion.

Many old flawed url parameters have been removed: useTimezone, useJDBCCompliantTimezoneShift, useGmtMillisForDatetimes, noTimezoneConversionForTimeType, noDatetimeStringSync.

If you live in a country with DST and you want to store precise timestamp you have to change the MySQL global timezone. This is a shortcoming of MySQL that accept/retrieve data in locale format without the DST flag.

I tested insert/select from both preparedStatements and serverPreparedStatement on MySQL 5.0. More test and feedback is welcome.
[11 Oct 2006 10:33] Alex Dupre
Well, the patch can be found at http://www.alexdupre.com/mysql-connector-java-src.diff.gz and has to be applied to mysql connector 5.0.3 sources.
[13 Nov 2006 13:28] Alex Dupre
Here is the patch for 5.0.4 release:

ftp://ftp.freebsd.org/pub/FreeBSD/ports/local-distfiles/ale/mysql-connector-java-5.0.4-tim...
[15 Nov 2006 18:42] Mark Matthews
Hi!

I'm looking at your patch. One configuration property that can't be removed is "noDatetimeStringSync", as it's a presentation thing, and it's how some folks have to deal with the all-zero datetime/timestamp that MySQL has that can't be represented as a java.sql.Timestamp.

  -Mark
[15 Nov 2006 19:33] Alex Dupre
Thanks for your attention.
The removal of that option was only a consequence, since I saw in the code that useTimezone && noDatetimeStringSync couldn't be enabled together, but I didn't look deeply at what that option really does. 99.9% of the patch deals with timezone conversions, and this is what I tried to fix (successfully imho).
If you find it correct/useful, we can discuss on further improvements and you can re-add such option that I've ignored.
Waiting for your feedback.
[28 Nov 2007 21:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38756
[28 Nov 2007 22:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38758
[16 Dec 2008 5:48] Gili T.
Please improve the documentation for useLegacyDatetimeCode found here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html

It's isn't immediately clear from this documentation that with the switch set to true MySQL will discard all time-zone information associated with Calendar. This is very serious and needs to be documented explicitly alongside this switch.
[16 Jul 2009 12:54] Tony Bedford
An entry was added to the 5.1.5 changelog:

When calling setTimestamp on a prepared statement, the timezone information stored in the calendar object was ignored. This resulted in the incorrect DATETIME information being stored. The following example illustrates this:

Timestamp t = new Timestamp( cal.getTimeInMillis() );
ps.setTimestamp( N, t, cal );
[17 Oct 2014 7:06] Benjamin Labonte
One of the issues that has been fixed for the PreparedStatement is that now the time zone of the Calendar specified by the setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal) call is used to format the given Timestamp.

Why is this not the case for the ServerPreparedStatement?
The corresponding method setTimestampInternal(int parameterIndex, java.sql.Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward) simply ignores the given time zone tz if the legacy code is switched off.

Is this still a bug?