System.Data.SQLite

When sending in an UTC date (DateTime.Kind set as UTC) to SQLite with default options (connection string DateTimeKind unspecified, DateTimeFormat not specified so ISO8601), the System.Data.SQLite driver store it as UTC but convert it back to local on reading, corrupting the date time part.

When reading date as unspecified, I would expect it to not perform any conversion. Isn't it the very meaning of unspecified kind? It can be UTC or Local, no assumption, only the apps may know.

But there the app received a shifted date, forbidding to use SQLite to store UTC dates in some columns and local dates in some other columns. (Converting back to UTC may not always be possible, due to ambiguous times at DLS days.)

This is very similar to this ticket https://system.data.sqlite.org/index.html/tktview/fec33d125c1f703f61df but this ticket answer does not address the case of application having to handle both local and UTC dates.

A workaround could be to force on application side the date kind to unspecified before writing it for avoiding having the driver converting it to local on reading, but we should not have to do that.

The trigger of this concern is indeed this NHibernate ticket: https://github.com/nhibernate/nhibernate-core/issues/1362
Most databases are agnostic with DateTime.Kind, they does not take it into account when writing, and yield it back unspecified kind on reading, time part unaltered. SQLite is our only tested database which breaks this when it receives UTC dates by shifting the time part on reads. Switching its connection string to Utc causes many other tests to start failing, due to unspecified dates being then stored as Utc and other similar consequences.

Another workaround would be to specify a format string preventing SQLite to store the date with an UTC marker, thus avoiding the undesired conversion to local when reading them as unspecified.
Adding `DateTimeFormatString=yyyy-MM-dd HH:mm:ss.FFFFFFF;` does that.

It would certainly be nice if we had the flexibility to change the DateTime
semantics used when the DateTimeKind is unspecified; however, for reasons of
backward compatibility, we cannot change them.

When storing DateTime values, it is *highly* recommended that you use UTC in
the datebase and then perform any local time conversions yourself in your
application code.

I understand this will be a breaking change for applications keeping the default unspecified output of System.Data.SQLite while at the same time using UTC dates, and having adapted on reads to the resulting time shift and actual kind change from UTC to Local occurring under "unspecified".

But this pattern does not look as a reasonable thing to keep forever. Maybe the breaking change could be handled with a backward compatibility setting allowing applications needing it to revert to old behavior. Of course, this add some complexity to the code.

The suggestion of storing only UTC dates implies to use the DateTimeKind Utc setting anyway, suggesting indeed te default "Unspecified" is not a sensible default. But in fact I disagree: there are real-use cases which mandate local time instead of UTC such as future events, which may be non-sens expressed in UTC or may get corrupted by a DLS law change before they occur, if stored in UTC. See https://stackoverflow.com/a/2532962/1178314 for a detailed list of cases where UTC might or might not be the right choice. 

Being able to store both reliably, depending on what are the business needs, should not be considered as promoting a bad practice.

There is real valid business needs for having an application able of storing both UTC and local dates. A lack of support of this is not a good thing.

In that case, perhaps you could store seconds since the epoch and place whatever
time zone manipulations are necessary in the application code?