EF6 and MySQL timezone support

Question

I am storing datetimes at the UTC format (using DateTime.UtcNow) in a MySQL database. After some manipulations of the entity containing that dates, it becomes a mess.

Here is how I defined my table:

CREATE TABLE `gsrep`.`GlobalVersion` (
  [..],
  `CreationDate` TIMESTAMP NOT NULL,
  `LastUpdateDate` TIMESTAMP NOT NULL,
  [..]
);

I am running the framework .NET 4.5.2 and I mapped my database with Entity Framework 6 and Database first principle.

Here is what I am doing:

First step, create the object:

var now = DateTime.UtcNow;
var globalVersion = new GlobalVersion
{
    CreationDate = now,
    LastUpdateDate = now
};
// saving the object

Let's say it is 10:00 am in my country and I am GMT +2. The created dates have their Kind property set to DateTimeKind.Utc and their value set to 08h00 am. In the database, the dates value are 08:00 am.

Everything is fine.

Second step, get the object:

Using another connection, when I get the object from the database, the date are set to 08:00 am but their Kind property are set to DateTimeKind.Local.

This is not totally fine, but while I am only reading the data, this is not a problem. I did not even notice it until I need to change one date.

Updating one date:

And here comes the mess. At one moment, I need to change only one date. Let's say it is now 11:00 am in my country.

// getting the object
globalVersion.LastUpdateDate = DateTime.UtcNow;
// saving the object

Once saved, the LastUpdateDate is set to 09:00 am in the database (which is fine) but the CreationDate is now set to... 11:00 am. It looks like it is set to DateTime.Now at the DbContext.SaveChangesAsync() (I am saying that because if I pause during the debug between the DateTime.UtcNow and the SaveChangesAsync instruction, the CreationDate is set to the moment I click on continue).

Absolutely nothing in my code changes the CreationDate... Before the call to DbContext.SaveChangesAsync(), the CreationDate has the unchanged expected value. Right after, the CreationDate is set to the moment I clicked on continue (only in the database, in the EF cache, the value is still the same, but at the next connection, if will take the value in the database).

I am absolutely lost with this behavior... What could cause that?

I am writing the same operations in parallel in a SQLite database (I set the DateTimeKind to Utc in the connection string, and I do not have the problem).

Based on your description it sounds like EF is ignoring the UTC offset from the mysql timestamp when retrieving, then updating even the field you didn't change. Possible hint: https://stackoverflow.com/questions/6931014/is-it-possible-to-prevent-entityframework-4-from-overwriting-customized-properti/9386364#9386364 — joshp, Jun 12 '17 at 17:12
@joshp the solution provided in your link fix the `DateTime.Kind` but the CreationDate is still overridden during the `SaveChangesAsync` ... — fharreau, Jun 13 '17 at 12:49

fharreau · Accepted Answer · 2017-06-14T07:49:48.153

Wow... though I was creating my table with this script:

CREATE TABLE `gsrep`.`GlobalVersion` (
  [..],
  `CreationDate` TIMESTAMP NOT NULL,
  `LastUpdateDate` TIMESTAMP NOT NULL,
  [..]
);

MySQL Workbench (or MySQL ?) actually created a table like that (get it using the MySQL Workbench reverse engineering):

CREATE TABLE `GlobalVersion` (
  [..],
  `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LastUpdateDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  [..]
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It had no sense that Entity Framework update the CreationDate on his own. Finally, this as nothing to do with the DateTimeKind issue. To solved it, I follow the solution proposed in the joshp's link but with some improvements.

EF6 and MySQL timezone support

1 Answers1