Bug #45342 Server sends timezoned timestamps over the wire
Submitted: 4 Jun 2009 20:57 Modified: 8 Jun 2009 17:56
Reporter: Roberto Jimeno Email Updates:
Status: Verified Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:5.1.30 OS:Any
Assigned to: CPU Architecture:Any
Tags: GMT, timestamp, timezone, UTC

[4 Jun 2009 20:57] Roberto Jimeno
This is linked to bug #45340 that will not be fixed as they made clear it is a bug in the server.

When getting a TIMESTAMP column the server seems to send a timezoned timestamp instead of a plain UTC timestamp.

How to repeat:
If I do these commands on a server configured with a timezone different than UTC (like CET) we see a timestamp in the server timezone even having the client in UTC.

CREATE TABLE mysqltimestamptest (mysqltimestamptest TIMESTAMP NULL);
INSERT INTO mysqltimestamptest (mysqltimestamptest) VALUES (NOW());
SELECT mysqltimestamptest FROM mysqltimestamptest;

Suggested fix:
ALWAYS, send timestamp values in UTC over the wire. Sending a timezoned timestamp makes not sense.
It seems MySQL has tons of bugs with timezone support.
Timezones are not the business of a database. Timezone is the business of the presentation layer. So, timezones should always be stored in UTC, sent in UTC over the wire, and saved in UTC in backups.
Do you guys have any intention to make UTC in backups the only option?
And fixing this bug?
[4 Jun 2009 21:33] Sveta Smirnova
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/time-zone-support.html:

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. 

This means TIMESTAMP values stored in UTC already and server sends them in client timezone.

Please confirm if you want TIMESTAMP values to be not converted to session time zone, but sent as they stored == UTC.
[4 Jun 2009 22:03] Roberto Jimeno
My client is in UTC but I am getting timestamps with the timezone of the server and even without a timezone specification (like +0200)

I just want my Java code (at bug #45340) to work correctly.

I suggest various ways on how to fix this bug:
-If the client does not specify what timezone he is in, always send timestamps in UTC. (This will bother people using old clients that cannot display timestamps in local timezone)
-In MySQL Connector/J always start the connection telling the server to send UTC timestamps. (This would fix the bug but all correctly written clients need UTC timestamps)
-Document that MySQL server should be always run on a UTC configured server and prevent it from running on a timezoned server. (This will bother people that want to have the server user interface in their timezone)

So, yes timestamps should always be stored UTC on the server and sent in UTC to the client.
[5 Jun 2009 6:07] Sveta Smirnova
Thank you for the feedback.

Combination of stmt.executeUpdate("set time_zone='+00:00'"); and configuration property useJDBCCompliantTimezoneShift=true fixes this problem for your test. So everything works correctly if client settings are correct too. Although I consult with Mark before make this report "Verified" or "Not a Bug"
[8 Jun 2009 12:47] Roberto Jimeno
I do not want to modify my code to add stmt.executeUpdate("set time_zone='+00:00'"); everywhere.
How do you recommend configuring a standard Web Application Server like Tomcat to fix this bug?

Will you consider fixing this bug so neither stmt.executeUpdate("set time_zone='+00:00'"); and useJDBCCompliantTimezoneShift is needed?
[8 Jun 2009 17:56] Sveta Smirnova
Thank you for the feedback.

Verified as feature request. Thought you can use UTC as server TZ, or use DATETIME.

> How do you recommend configuring a standard Web Application Server like Tomcat to fix this

Try to use init_connect (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_init_connect) or init-file (http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_init-file) Regarding Web Application Server like Tomcat consult its documentation.
[22 Jun 2010 10:40] Steve Thomson
I agree that having the timestamp returned to the client in UTC is the only logical way that this can work.

> [8 Jun 2009 19:56] Sveta Smirnova
> Verified as feature request. Thought you can use UTC as server TZ, 
> or use DATETIME.

DATETIME doesn't allow `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` so I suggest either removing that restriction or this one.

I need an auto-updating timestamp that is stored in UTC and returns as UTC. Changing the server time or adding triggers are not acceptable solutions.