Utc Date saving as Local date in Sqlite

Question

I have an Sql database which contains a date field.

I use Dapper to update the database as follows:

const string sql = "UPDATE AdminDb.Users " +
                   "SET IsLoggedOn = 1, LastLoggedOn = @LastLoggedOn " +
                   "WHERE Username = @username";
var date = DateTime.UtcNow;
DatabaseConnectionBase.DatabaseConnection.Execute(sql, new { username, LastLoggedOn = date });

I am finding to my great annoyance when breaking before the actual update, the date variable reads 30/3/2015 9:32:54 however when I run the update the database saves the date as 30/3/2015 10:32:54

As the UK yesterday changed from GMT to BST (UTC +1) I am sure that the database seems to be trying to compensate for this as this issue never came up before.

I thought I had averted this sort of issue by using the DateTime.UtcNow property to save my date.

This is causing serious issues when validating users.

• I am sure it is not my code as the date is correct going into the Dapper Execute method.
• I can't see why Dapper would try to compensate as most developers would be screaming at such functionality
• Which leads me to the conclusion that it must be something in Sqlite that is causing this issue. Perhaps there is a pragma I need to run?

As per a suggestion from another site I tried formatting the date as follows:

var date = DateTime.UtcNow.ToString("o");

The intention being to force the date into ISO-8601 format but I had no luck with that.

Has anyone got any ideas?

Answer 1

Thomas Levesque has the solution here:

// You just need to specify DateTimeKind=Utc in your connection string:
string connectionString = @"Data Source=D:\tmp\testSQLiteDate.db;DateTimeKind=Utc";

Answer 2

This happened to me too. What I did was serialize the datetime to a string myself before adding it as a parameter.

internal const string DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
cmd.Parameters.Add("@Threshold", DbType.DateTime).Value = threshold.ToString(DateTimeFormat);