|Title:||Bug retrieving DateTime with System.Data.SQLite for UTC dates|
|Last Modified:||2015-03-24 22:10:07|
|Version Found In:||22.214.171.124|
anonymous added on 2015-03-20 21:49:49:
I'm using the 126.96.36.199 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!