MySQL.Data DateTime.Kind being different between SQL DATETIME and TIMESTAMP

Question

So I updated to MySql.Data 8.0.13 and I noticed that my TIMESTAMP fields come as DateTimeKind.Local and the DATETIME fields are read as DateTimeKind.Unspecified

I thought that "Timestamp" would be a great semantic type to represent a "CreatedTime" field, so I went with it.

The thing is, I usually write these dates "manually" in the insert queries, they're generated by the application, in UTC, to make sure that local time and their crazy timezones/daylight savings won't break anything. I don't use on-update or on-insert on them. And now I'm getting problems with the application's JSON serializer converting an already UTC time to UTC again, which causes a wrong offset time.

So my question:

Is there a way to enforce MySQL.Data to "Consider those dates/timestamps as being UTC"? Or is it best for me to discontinue the usage of TIMESTAMP and adhere to DATETIME instead? Or should I pull a crazy gimmick like this?

if(databaseValue is DateTime dt) {
     databaseValue = new DateTime(dt.Ticks, DateTimeKind.UTC);
}

Which would be the preferable way to solve this problem?

Which way most effectively satisfies your specific requirements? — Robert Harvey
– Robert Harvey, Commented Dec 7, 2018 at 23:34
Ok i did some research and it seems mysql connector .net doesnt support any timezone option in the Connection String, to make it I'd have to issue SET on every connection I opened and make obnoxious checks, so I'll have to go with my convictions and force the microorm to ignore the DateTimeKind specified by the dbms and consider it as always having been UTC. "If this date isn't UTC then it was saved wrong", I'm sure many could see heresy in what I'm going to do, but life is easier in UTC. — Felype
– Felype, Commented Dec 8, 2018 at 0:16
I am seriously annoyed by this though, I really wish and hope for an elegant and adequate alternative — Felype
– Felype, Commented Dec 8, 2018 at 0:21

Mark G · Accepted Answer · 2018-12-14 04:12:21Z

1

There's an alternative library MySqlConnector which has a DateTimeKind option you can specify in the connection string:

The DateTimeKind used when MySqlDataReader returns a DateTime. If set to Utc or Local, a MySqlException will be thrown if a DateTime command parameter has a Kind of Local or Utc, respectively.

answered Dec 14, 2018 at 4:12

Mark G

3,1441 gold badge29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Felype Over a year ago

I've been fiddling with this and it seems fairly robust and adequate, thanks.

Collectives™ on Stack Overflow

MySQL.Data DateTime.Kind being different between SQL DATETIME and TIMESTAMP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related