MySQL Bugs: #45342: Server sends timezoned timestamps over the wire

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:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.1.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	GMT, timestamp, timezone, UTC

Description:
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?

Thank you for the report.

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

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

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.

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.

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"

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?

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
bug?

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.

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.