Bug #92367 MySqlDataReader.GetDateTime and GetValue return inconsistent values
Submitted: 11 Sep 2018 15:54 Modified: 12 Sep 2018 5:52
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.12 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Other (x64)

[11 Sep 2018 15:54] Bradley Grainger
Description:
When a TIMESTAMP column value is retrieved from MySQL, it's returned by Connector/NET as a System.DateTime object. The DateTime.Kind property of the returned object is different depending on whether it's retrieved by MySqlDataReader.GetDateTime() or MySqlDataReader.GetValue().

I would expect the DateTime.Kind property to be the same no matter how the value is retrieved from the reader. Secondly, based on bug #66964, I would expect the DateTime.Kind to be DateTimeKind.Local, so that the time can be intepreted correctly; having it set to Utc is wrong.

How to repeat:
Use a MySQL Server with a non-UTC time zone. (E.g., @@time_zone is SYSTEM and the server is running in PDT.)

Create a table with the following schema:

CREATE TABLE DateTimeTest (
  RecordId INT NOT NULL PRIMARY KEY,
  ModifiedDate TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Then execute the following C# code:

using (var connection = new MySqlConnection("..."))
{
	connection.Open();

	using (var cmd = new MySqlCommand("insert ignore into DateTimeTest(RecordId) VALUES(1);", connection))
		cmd.ExecuteNonQuery();

	using (var cmd = new MySqlCommand("select ModifiedDate from DateTimeTest;", connection))
	using (var reader = cmd.ExecuteReader())
	{
		reader.Read();

		// prints Local
		Console.WriteLine(reader.GetDateTime(0).Kind);
		
		// prints Utc
		Console.WriteLine(((DateTime) reader.GetValue(0)).Kind);

		// prints Utc
		Console.WriteLine(reader.GetMySqlDateTime(0).GetDateTime().Kind);
	}
}

Suggested fix:
Return local dates with DateTimeKind.Local no matter how they are retrieved from MySqlDataReader.
[12 Sep 2018 5:52] Umesh Shastry
Hello Bradley,

Thank you for the report and test case.
Observed with VS 2017 (C#.Net) and Connector/NET 8.0.12 version.

regards,
Umesh