DateTimeOffset is not handled in MySqlParameter

[sebastienros]

It throws on https://github.com/mysql-net/MySqlConnector/blob/master/src/MySqlConnector/MySqlClient/MySqlParameter.cs#L169

[caleblloyd]

MySQL does not have a type that maps 1:1 with DateTimeOffset. A DateTimeOffset has both a DateTime portion and an Offset (time zone) portion. By storing a DateTimeOffset object to a MySQL DATETIME type, you loose the Offset (time zone) portion of the object.

I think that it is why DateTimeOffset is not supported in Oracle's connector and not supported as of yet in MySqlConnector.

The closest solution I've seen using a single MySql field is converting DateTimeOfffset objects to UTC DateTime upon inserting into the database, and retrieving them from the database as UTC DateTime before converting back to DateTimeOffset. The Offset (time zone) portion of the DateTimeOffset object is stripped, but the time is still always stored as an instantaneous time (UTC).

@bgrainger what do you think we should do here? Implicitly convert DateTimeOffset to UTC DateTime? Or continue to throw an exception and leave it up to the caller to convert to DateTime and store their time zone information another way?

[sebastienros]

The current solution is only unblocking me as I do only store UTC values in my app.

I agree with the suggestion, storing and retrieving in UTC implicitly is the best solution IMO. I just couldn't find where the "retrieve" logic is in the code.

[caleblloyd]

Here's a sample of how it's done in the Pomelo.EntityFrameworkCore.MySql Entity Framework ORM

• Convert To UTC DateTime when storing
• Convert From UTC DateTime when retrieving

[bgrainger]

I can imagine three possible approaches for handling DateTimeOffset:

1. Ignore the Offset and store just the DateTime property
2. Allow DateTimeOffset only when the Offset is 0 (UTC)
3. Allow any DateTimeOffset value but convert it to UTC before storage

(1) obviously has silent data loss, which is unacceptable. (2) can roundtrip its accepted values, but would have to throw an exception at runtime for unacceptable values, which could be surprising behavior. (3) accepts any input (as long as it falls within MySQL's range) but can't roundtrip the Offset, which also feels like silent data loss (in a way).

I still have some questions of the value of providing serialisation for DateTimeOffset when its key advantage over a plain DateTime can't be persisted.

If solutions (2) and (3) would both work for @sebastienros then I think I lean a bit more towards (2) only because I dislike silent data loss more than I dislike functions that only work on a subset of valid input values.

[sebastienros]

In the current state of the PR I convert the DateTimeOffset to UTC automatically (3).

The issue I am facing is on the deserialization part. We only get the type of the MySql column for reference, so it's impossible to know if it's representing a DateTime or a DateTimeOffset.
Two issues:

• We are already loosing the Kind property of a DateTime value which can be Utc, Local or Unspecified and always defaults to Unspecified on the way out.
• There is no way to tell if it was saved as a DateTimeOffset and represents UTC.

Pomelo is solving it because it's at a level it knows about DateTimeOffset and can apply the transformation in both ways. I think this is how it should be solved. In my case I am using Dapper so I can also transform the value when it's loaded (http://stackoverflow.com/a/39730278/142772), but we still need to accept DateTimeOffset values without throwing an exception, so I would suggest to implement (3) without doing anything on the way out and actually losing information, as DateTime does, if the caller is not aware of the behavior.

[bgrainger]

We are already loosing the Kind property of a DateTime value which can be Utc, Local or Unspecified and always defaults to Unspecified on the way out.

I realised this shortly after writing my comment above. I agree this is also bad, but it seems that there's little we can do about it at the DB level; the caller will always need to be aware of this limitation and adjust the kind as necessary.

[sebastienros]

the caller will always need to be aware of this limitation and adjust the kind as necessary

Exactly, and this is why I am suggesting to also accept DateTimeOffset instead of throwing an exception

[bgrainger]

Fixed in 0.12.0.