System.Data.SQLite

The new default DateTimeFormat will append a 'Z' when inserting DateTime values of type UTC. This will cause a "String was not recognized as a valid DateTime." exception when trying to read value as DateTime (tested on 1.0.76.0) when also using the default DateTimeFormat.

I could not find any information about this breaking change making it hard to understand where to begin the troubleshooting.

A solution I found was to use DateTimeFormat=InvariantCulture in the older version (better to update the assembly reference since it has to build anyway). I found no DateTimeFormat I could use in the newer version to ensure backwards compatability. The only working method I found was to make sure no DateTime value written to the database was of UTC kind.

anonymous added on 2011-12-14 11:49:20 UTC:
I also found out that the problem goes the other way around. I read the following values using 1.0.77.0:

------------------------------

DateTime.UtcNow saved using 1.0.76.0

2011-12-14 10:41:28.7054394 <-- string

2011-12-14 10:41:28 (Unspecified) <-- DateTime

------------------------------

DateTime.UtcNow saved using 1.0.77.0

2011-12-14 10:41:28.8554396Z <-- string

2011-12-14 11:41:28 (Unspecified) <-- DateTime

------------------------------

As you might figure out my local timezone is +1.

Setting DateTimeFormat to InvariantCulture on the reader would bump both values to local timezone, but still give me a DateTime value with Kind = Unspecified.

Another test where I use 1.0.76.0 to read (with InvariantCulture since Default would crash) instead gives me:

------------------------------

DateTime.UtcNow saved using 1.0.76.0

2011-12-14 11:35:46.1148823 <-- string

2011-12-14 11:35:46 (Unspecified) <-- DateTime

------------------------------

DateTime.UtcNow saved using 1.0.77.0

2011-12-14 11:35:46.2628908Z <-- string

2011-12-14 12:35:46 (Local) <-- DateTime

------------------------------

The whole DateTime situation seems rather confusing, and DateTime.Kind seems to have broken with 1.0.77.0...

mistachkin added on 2011-12-15 06:03:00 UTC:
Unfortunately, adding the "Z" was required to fix several long-standing issues with correctness of the DateTime handling in System.Data.SQLite. If you use the same version for both applications, do you still see any issue?

anonymous added on 2011-12-15 10:06:38 UTC:
Using 1.0.77.0 to read DateTime.UtcNow saved with:

---

1.0.76.0 (DateTimeFormat=Default):

(string)2011-12-15 09:48:53.3128721

(DateTime)2011-12-15 09:48:53 (Unspecified)

---

1.0.77.0 (DateTimeFormat=Default):

(string)2011-12-15 09:48:53.4628806Z

(DateTime)2011-12-15 10:48:53 (Unspecified)

---

Meaning yes, there's a problem. Since we can't tell what version of SQLite wrote the DateTime value and since DateTime.Kind is Unspecified, we can't be sure of the time zone used unless reading the actual string value and parse that. That is not a viable solution with EntityFramework etc.

Saving DateTime.Now would both write/read as the local time, still with DateTime.Kind as Unspecified.

anonymous added on 2011-12-15 10:58:24 UTC:
To simplify the problem:

A) The default DateTimeFormat (for the SQLiteConnection) will cause exception if trying to read values in older (at least 1.0.76.0) assemblies!

B) Reading DateTime values pre 1.0.76.0 would give you a value with the same time zone you saved it with. If you wrote a value with DateTimeKind.Utc then that's what you would read. If you wrote a value with DateTimeKind.Local then that's what you would read. Of course there would be no way of knowing what time zone was used (since the DateTime struct will have Kind=Unspecified) unless you knew exactly what it got saved as. The best practice would be to ALWAYS write values in their UTC format and simply force read values to have DateTimeKind.Utc, making sure any conversion between time zones are done correctly. In 1.0.77.0 the values are ALWAYS converted to the local time zone, meaning the old method would apply an extra conversion into local time, giving invalid values.

Solution:

1. Make sure that DateTime.Kind is properly set to either Local or Utc when reading DateTime values. In my suggestion of best practice, old values would incorrectly read as Local with this fix, but developers could simply force Utc onto these values. For any development done past this fix, developers could rely on the DateTimeKind set when reading the value.

2. Announce this problem so developers have a chance to adapt and hopefully avoid any problems it could cause.

mistachkin added on 2011-12-16 05:13:19 UTC:
The old behavior was broken and I did not realize people were relying on it.

However, the previous default value was technically Unspecified as far as the DateTime conversions are concerned.

Changing of the recognized string formats (by adding the trailing "Z") is technically incompatible with previous versions; however, there was no nice way around it while still correcting the bugs seen via the DbDataAdapter (in the .NET Framework) and how it wants to format queries.

Another way around the issue is to avoid using the DATETIME column type in the database schema itself (i.e. using TEXT instead). Alternatively, consider storing DATETIME values in the database as the number of seconds since the epoch of the julian day number to avoid these types of formatting issues.

anonymous added on 2011-12-18 17:38:33 UTC:
I fully agree that the old behavior was broken, since UTC times could not be told apart from local values. Since the values now should be detectable as either Local or UTC, I'll instead make a feature request for DateTime structs to have this value properly set when reading from database.

My memory is blurry, but I must've missed all those other possible DateTimeFormats one could set on the connection string, or just assumed that the default would be the best way to go with when we started this development. Since our files are traveling between systems of different time zones it's important that we always can resolve to the UTC time.

For now I'll refrain from using 1.0.77.0. I either have to update all existing files (5000+ spread over multiple remote systems) or add custom handling before using any DateTime values. Since I do increment the user_version for any change in the structure, this could be used to tell what files are saved using older SQLite. Anyways, this is my problem. Thanks for your time!