System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: fec33d125c1f703f61dff42c6c814b0487660ac2
Title: Bug retrieving DateTime with System.Data.SQLite for UTC dates
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: LINQ Resolution: Works_As_Designed
Last Modified: 2015-03-24 22:10:07
Version Found In: 1.0.96.0
User Comments:
anonymous added on 2015-03-20 21:49:49:
I'm using the 1.0.96.0 version of the .NET NuGet packages System.Data.SQLite, System.Data.SQLite.Core, System.Data.SQLite.EF6, and System.Data.SQLite.Linq and I discovered the following bug today.  

I have a SQLite table with a datetime column.  In my C# code I set this tables datetime column via LINQ with a UTC datetime value.  

Example
Table.DateTimeColumn = DateTime.UtcNow;

The data gets correctly stored in the SQLite database with a Z at the end of which tells me it is saved as UTC. 

The problem comes in when I retrieve the date from the database via another LINQ query.  In this case the DateTime comes out of the database automatically converted to local time.  Even worse, the DateTime.Kind property is set to "Unspecified" so in code I have no way of knowing whether the time is in UTC or not.  

SQLServer, and the DevArt dotconnect for SQLite software, return the UTC date correctly; however, the System.Data.SQLite libraries return this in local time.  I'm wondering if this is as simple as your library specifying the DateTime.Kind.

Any chance you can fix this in the next update?  This is causing me to rewrite my applications to assume all dates coming out of the database are in local time regardless of how they are stored.

Tony Alwardt

mistachkin added on 2015-03-21 00:33:46:
What values are you using for the "DateTimeFormat" and "DateTimeKind" properties
of the connection string?

mistachkin added on 2015-03-21 00:34:08:
Also, do you have a short example that demonstrates the issue?

mistachkin added on 2015-03-21 02:11:06:
Also, when using LINQ, you can set the environment variable:

    AppendManifestToken_SQLiteProviderManifest

To quote from the docs:

If this environment variable is set [to anything], it will be used
by the System.Data.SQLite.Linq.SQLiteProviderManifest class (and the
System.Data.SQLite.EF6.SQLiteProviderManifest class) to modify future
provider manifest tokens by appending the value of the environment
variable to the existing provider manifest token, if any. Typically,
in order for the constructed provider manifest token to be syntactically
correct, the environment variable value [to be appended] must begin with
a semicolon.

Basically, you can include the DateTime related connection string properties
in that environment variable value, thus making the System.Data.SQLite.Linq
assembly honor them.

anonymous added on 2015-03-23 20:38:20:
Thank you for the quick response.  I added the DateTimeKind=Utc to my connection string and things started working as expected.  I didn't realize that the DateTimeKind item was available as part of the connection string.
 
Thanks!