MySQL Bugs: #66964: TIMESTAMP values are mistakenly represented as DateTime with Kind = Local

Bug #66964	TIMESTAMP values are mistakenly represented as DateTime with Kind = Local
Submitted:	26 Sep 2012 1:35	Modified:	12 Oct 2012 19:11
Reporter:	Pavel Bazanov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.5.4	OS:	Any
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any
Tags:	datetime, local, time_zone, timestamp, timezone, unspecified

Description:
Since version 6.5 (and closing bug #63812) connector returns TIMESTAMP values as a DateTime object with the Kind property = Local. Previously it was = Unspecified.

This is often wrong and may lead to bugs.

How to repeat:
Let's say our server is in London (UTC+1 in summer) and our client is in UTC+4 timezone. By default time_zone variable is set to SYSTEM, which means client's timezone is considered the same as server's timezone.

Let's say the timestamp value in the database is 2012-09-26 01:00 UTC.
If we connect to the server and read that value we will get a DateTime object with the value 2012-09-26 01:00 and Kind = Local. But that is not true! That is not local time, that is server's time!

Of course, it may lead to bugs later. For example, if we serialize that value to send it to a web service we will get "2012-09-26T01:00:00+04:00" (+4 = client's time zone), which is incorrect, because the original value is 2012-09-26 01:00:00 UTC.

Suggested fix:
Possible options and considerations:
- Set Kind = Local only if time zone is not default SYSTEM, but set explicitly.
- By default (time zone is not set explicitly and = SYSTEM) convert timestamp values according to client's timezone. For example in the example above, the client should get 2012-09-26 04:00:00 with Kind = Local.
- Make it an option (set trough a connection string for example) so a client would be able to control this behavior and choose one that better suits his situation and needs.
- Default time zone should not be set to the value SYSTEM, it should be set to a client's time zone.

Hi,

First some clarification, the following server variables control time zone translation in the server:
- system_time_zone, time zone in the server
- time_zone, time zone in the client (Connector/NET in this case)

The server is in charge of translating the dates automatically, not the client. This makes sense because the server knows what date is sending, therefore the query:
select curdate(), utc_date()
returns different values if UTC != time_zone.

On the other hand, there's a real bug in Connector/NET such that MySqlDataReader.GetDateTime() returns a date of kind Local always (should be UTC when time_zone is utc)

That is addressed in this fix
if time_zone is UTC, Kind is UTC, otherwise Kind is Local.

That way you can enable your scenario of working with multiple servers with different timezones by changing time_zone (client timezone) to UTC in all MySqlConnection

(One simple way to do it is with the command
set @@global.time_zone = '+0:00', 
then every new connection you open (or after closing and reopening the current one, you will get those connections to use the new client time zone).

Bear in mind that with this fix, you will not have to change system_time_zone of any of your server.
Connector/NET will check if client time zone differs from UTC by running something like select timediff( curtime(), utc_time() )
(where are return value of zero hours mean UTC being used for time_zone).

With Kind = UTC, you can use .NET standard APIs to translate between timezones for frontend apps in case it is required.

Added to changelog for 6.6.4: 

Since Connector/Net 6.5, TIMESTAMP values have been returned as
DateTime objects with a kind property of Local rather than
Unspecified. MySqlDataReader.GetDateTime() should have returned a
date with a kind property of UTC when the time_zone connection
property was utc. With this fix, if time_zone is UTC, Kind is also
UTC; otherwise, Kind is Local. To work with multiple servers with
different timezones, change the time_zone setting to UTC in all
MySqlConnection objects. For example, if you issue the command:

set @@global.time_zone = '+0:00'

then every new connection you open, or
the current connection if you close and reopen it, will use the new
client time zone. With this fix, you will not have to change
system_time_zone of any of your servers. Connector/NET checks if
client time zone differs from UTC by running a query like:

select timediff( curtime(), utc_time() )

where a return value of zero hours
means UTC is being used for time_zone. With Kind = UTC, you can use
.NET standard APIs to translate between time zones for frontend
applications when required.