Issue with SQLite and DateTime Accuracy

Question

I am using SQLite.NET with Extensions in a UWP app to store an object containing a DateTime field and I'm getting weird results. It seems that dates are stored with a few hours off from what they should be, sometimes pushing the Day of the DateTime to the next day.

I am storing a POCO class called Record which contains the Situation object that looks like this

public class Situation
{
    [PrimaryKey, AutoIncrement]
    public int SituationId { get; set; }

    public DateTime DateTime { get; set; }

    public string Description { get; set; }
}

The Record class, containing the situation is stored using SQLite by way of a repository pattern like so (I have only included the relevant methods):

 internal class Repository<T> : IRepository<T> where T : class
{
    private SQLiteAsyncConnection asyncConn;

    public Repository(SQLiteAsyncConnection conn)
    {
        asyncConn = conn;
    }

    public async Task<T> GetByIdAsync(int id)
    {
        var entity = await asyncConn.GetWithChildrenAsync<T>(id);
        return entity;
    }

    public async Task InsertOrUpdateAsync(T entity)
    {
        await asyncConn.InsertOrReplaceWithChildrenAsync(entity);
    }
}

Finally, I get an AsyncConnection for the Repository using a ConnectionManager class:

public class ConnectionManager
{
    public static readonly string FileName = "db.sqlite";

    private static string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");

    public static SQLiteAsyncConnection GetAsyncConnection()
    {
        var connString = new SQLiteConnectionString(path, storeDateTimeAsTicks: true);
        var connWithLock = new SQLiteConnectionWithLock(new SQLitePlatformWinRT(), connString);
        return new SQLiteAsyncConnection(() => connWithLock);
    }
}

This AsyncConnection stores DateTimes as ticks, which I suspect may be the source of the issue.

In one case, immediately before the Record object is stored using Repository.InsertOrUpdateAsync, the Situation.DateTime has the following values:

DateTime = {2016-07-01 12:59:59 PM}

Ticks = 636029747990010000

However, pulling the Records using Repository.GetByIdAsync, DateTime values are as follows:

DateTime = {2016-07-01 4:59:59 PM}

Ticks = 636029891990010000

As you can see, something is up with the way SQLite is storing the DateTime. The Ticks field has changed resulting in a new date. I'm not 100% sure why this is. I know DateTime can have issues with accuracy, but if DateTimes are stored as Ticks, shouldn't the Ticks field match? Why do they get changed?

Assuming I must store DateTimes as ticks, how can I resolve this issue? I'm thinking of just setting the DateTime hour to 12 so it can increase or decrease by several hours without changing the day, but this is obviously not ideal.

Any help would be appreciated. :)

Have you tried using DateTimeOffset? The problem in your case could be the fact that DateTime does not store time-zone information — Martin Zikmund, Jul 16 '16 at 16:40
It totally looks like a timezone issue. Try using UTC dates to get rid of timezone issues when storing / reading dates. — redent84, Jul 18 '16 at 09:02

score 1 · Answer 1 · answered Jul 18 '16 at 10:50

1

I made a demo and using Ticks to store the DateTime. The same problem occurs. I debugged the DateTime Property of received Situation Object. It turns out the DateTime.Kind is Utc. So it is a timezone problem, SQLite converts the DateTime to a UTC Time by default. To fix the problem, You can use DateTime.ToLocalTime to get the correct local time.

Here are the codes:

if (situation.DateTime.Kind == DateTimeKind.Utc)
{
     situation.DateTime = situation.DateTime.ToLocalTime();
}

answered Jul 18 '16 at 10:50

Elvis Xia - MSFT

10,801
1
13
24

Brilliant! Thanks! :) Since I don't care about the actual time in my case, I suppose I could also, as redent84 said, make all the DateTimes have a UTC DateTimeKind, correct? All I care about are the Year, Month and Day. The time was merely an issue since it sometimes pushed the Day ahead by 1. – AndrewPno Jul 18 '16 at 12:29
I don't get it. If you care about day, month and year of local time, UTC Datetime should be convert to local time. In certain scenario, day month or year won't be shown correctly by UTC. e.g. 6:00 am January 1. 2016(local in China) will be 11:00 pm December 31. 2015(UTC). – Elvis Xia - MSFT Jul 19 '16 at 01:29

Issue with SQLite and DateTime Accuracy

1 Answers1