Bug #63812 MySqlDateTime.GetDateTime() does not specify Timezone for TIMESTAMP fields

• Submitted: 20 Dec 2011 18:44
• Modified: 29 Feb 2012 19:16
• Reporter: Alex K.
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 6.4.4
• OS: Any
• Assigned to: Gabriela Martinez Sanchez
• CPU Architecture: Any

[20 Dec 2011 18:44] Alex K.

Description:
According to the MySQL 5 Reference Manual, the TIMESTAMP database field:
"TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval."

So, when using the MySqlDataReader.GetDateTime()-method a DateTime with no timezone is returned (DateTime.Kind=Unspecified) because MySqlDataReader.GetDateTime() casts the value to MySqlDateTime and calls its GetDateTime()-method, which simply does a "new DateTime(...)" without specifying the timezone.
However, the returned value from the MySQL-database for TIMESTAMP-fields is already converted to the Local Time of the server and as such the above method should initialize with "new DateTime(..., DateTimeKind.Local)".
This creates problems when later trying to convert the DateTime-object's time to another timezone such as theUTC time because the .NET-Framework does not know what to convert it from. Of course, as a result it will assume the unspecified time is in local time and produce the correct result. However, when calling DateTime.ToLocalTime() the .NET-Framework will assume the unspecified time is in UTC time (which is incorrect) and produce a wrong DateTime object.

How to repeat:
Using C# and MySQL 5, read a value from a TIMESTAMP field as a .NET DateTime object. Calling that object's "Kind" property will return "Unspecified" instead of "Local". This makes converting between timezones almost impossible.

Suggested fix:
This probably/maybe applies to saving DateTime-objects to TIMESTAMP-fields, too. I haven't checked.

[13 Jan 2012 18:00] Reggie Burnett

I think we will just make this change for timestamp values since a user can easily submit a datetime value that is already uTC.   We don't want to make any assumptions about the tz of the values users are storing in there datetime columns.

[13 Jan 2012 18:08] Alex K.

Yes, my intention was to have this fixed for TIMESTAMP fields because the documentation clearly states a timezone for them. The DATETIME field should continue to not specify a timezone because it, obviously, can be any timezone.

[29 Feb 2012 19:16] John Russell

Added to changelog for 6.5.0: 

The MySqlDataReader.GetDateTime() method was not recognizing that
TIMESTAMP values had already been converted to the local time zone of
the MySQL server, which could cause incorrect results if the value
was later processed through the ToLocalTime() method. The fix causes
the Kind property to be correctly set to Local rather than
Unspecified.