Utc Save Date as Local Date in Sqlite

Question

I have a Sql database that 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 find it very annoying to break before the actual update, the date variable reads 03/30/2015 9:32:54 AM , but when the update starts, the database saves the date as 3/30/2015 10:32:54 AM

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, since this problem has never occurred before.

I thought I prevented this problem by using the DateTime.UtcNow property to save my date.

This causes serious problems when checking users.

• I am sure this is not my code as the date is correct in the Dapper Execute method.
• I don’t understand why Dapper will try to compensate, since most developers will shout about such features.
• This leads me to the conclusion that it must be something in Sqlite that causes this problem. Perhaps there is a pragma that I need to run?

As per the suggestion from another site, I tried to format the date as follows:

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

The goal is to format the date in ISO-8601 format, but I had no luck with that.

Does anyone have any ideas?

Answer 1

. , , datetime , .

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

Answer 2

:

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

Answer 3

If you do the same with pure ADO.NET, does the same? Interestingly, this is a database item or provider item, not a library item. Dapper has ToLocalTime()either ToUniversalTime()calls - it transfers time unchanged. In SQL Server, the following works fine in configuring BST:

    public void SO29343103_UtcDates()
    {
        const string sql = "select @date";
        var date = DateTime.UtcNow;
        var returned = connection.Query<DateTime>(sql, new { date }).Single();
        var delta = returned - date;
        Assert.IsTrue(delta.TotalMilliseconds >= -1 && delta.TotalMilliseconds <= 1);
    }