Utc Date saving as Local date in Sqlite

Question

Utc Date saving as Local date in Sqlite

c# dapper datetime sql 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?

Steven Wood

View more on Stack Overflow

Marcom · Accepted Answer · 4/23/2015 4:58:04 PM

Accepted Answer

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);

Marcom

Marc Gravell · Accepted Answer · 3/30/2015 12:32:45 PM

If you do the same thing with pure ADO.NET, does the same thing happen? I wonder if this is a database thing or a provider thing, rather than a library thing. Dapper has to ToLocalTime() or ToUniversalTime() calls - it passes the time through unaltered. On SQL Server, the following works fine in a BST setting:

    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);
    }

Utc Date saving as Local date in Sqlite

Question

Fastest Dapper Plus Extensions

Accepted Answer

Expert Answer

Performance

Expression Evaluator