Description
Hello. First and foremost, thank you for your work on this awesome library.
I'm planning to move one of my projects from raw mysql-net driver to your EF library. During testing all details regarding compatibility an 68DD d consistency, I've stumbled upon my old arch-enemy, issue #487 that I opened in mysql-net.
In short:
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
"Current time zone" is declared as the time zone of the connection, which by default (and majority of cases) is server's time.
This works fine and is not an issue as long as both your MySQL server and .NET application share the same timezone, even more preferably the same server. In this scenario it's enough to convert all DateTimes to Local kind and execute in SQL query, where server will happily convert it to UTC for storage, and from UTC back to your local time for retrieval.
The situation complicates when we have MySQL server in one (non-UTC) timezone and the application in another one. The proper way to go about this is "agreeing" with the server during SQL query in which timezone we declare our datetime fields, so it can appropriately store the values in UTC.
In mysql-net, I've achieved this by prepending SET time_zone = '+00:00'; to each of my SQL queries, then ensuring that all my DateTimes are always converted to UTC kind. I did the same for retrieval, ensuring that MySQL returns UTC for me that I may convert to my local timezone if needed. Both requirements (UTC to and UTC from) could be further enforced with awesome DateTimeKind = MySqlDateTimeKind.Utc mysql-net connection string, which only needed SET time_zone = '+00:00'; on top of it, and you could be sure that storage and retrieval of timestamps worked regardless of timezone combinations of both machines.
And this is the problem I'm facing right now, since I can't easily "prepend" SQL query to each EF request. If I'm in e.g. +03:00 timezone, and my MySQL server is in +01:00, then regardless if I sent the server local or UTC time, it'd be saved wrong due to interpretation that whatever I'm sending is in +01:00, while in reality I can only use +03:00 (local) or +00:00 (UTC). The proper way to go is to either agree on UTC and send server UTC, or agree on my local timezone and send server local time, which it could then convert properly to UTC for storage itself.
I've done a few tests and it looks like just setting timezone in SQL query before action doesn't do anything (probably due to connection pooling, maybe state reset). The only way that I've found for now to make it work is to manually Database.OpenConnection() and Database.CloseConnection() within my DbContext, then I can set timezone in SQL query before doing what I need with the context. The problem with this solution is quite obvious - it doesn't only require from me to open/close database connection manually (I wouldn't need it if not for this workaround), but also forces me to do what I had to do in mysql-net, by setting timezone myself.
Therefore, my question to you, is there any possibility to somehow declare the time zone for the DbContext or in some other way prepend SQL query to each call, in order to ensure consistency in storage/retrieval of timestamps? If this is currently not possible, is there any interest to make it happen in one way or another? My current idea includes some DateTimeKind TimeZone setting with values of Unspecified (default), Local and UTC, with last two adding appropriate SET time_zone bits, of either current machine timezone, or +00:00 respectively.
If by any chance you'd want/need to reproduce my scenario, simply create a table with
TIMESTAMPfield, set your MySQL machine to +01:00 timezone and your .NET machine to +02:00. Then you can store and retrieve anyDateTimevalue in your app in order to find out that the retrieved one (+01:00) is not the one you stored (+02:00). This is because server interpreted your time as +01:00, while in reality you can only do local of +02:00 or UTC of +00:00, neither of which matches server's timezone.
Thank you in advance for answering.