mardi 9 juin 2015

DateTime returned by SQLite not the same as added to the DB

What I have

The following test code snippet creates a SQLite database for a type with just one property called Date. After that it connects to the database, stores a new Day object in there with the current date and afterwards queries for exactly that element.

using System;
using System.IO;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using SQLite.Net;
using SQLite.Net.Platform.Generic;

namespace StackOverflowQuestions
{
    [TestClass]
    public class SqLiteTests
    {
        private SQLiteConnection _connection;

        private class Day 
        {
            public Day() : this(DateTime.Now) { }
            public Day(DateTime date)
            {
                Date = date;
            }
            public DateTime Date { get; set; }
            public override string ToString()
            {
                return Date.ToString();
            }
        }

        [TestMethod]
        public void TestDateTime()
        {
            var databasePath = Path.Combine(Path.GetDirectoryName(System.Environment.CurrentDirectory), "database.db");
            _connection = new SQLiteConnection(new SQLitePlatformGeneric(), databasePath);
            _connection.CreateTable<Day>();
            var dateTime = DateTime.Now;
            var input = new Day(dateTime);
            _connection.InsertOrReplace(new Day(dateTime));
            var output = _connection.Query<Day>("SELECT * FROM day WHERE Date = ?", dateTime)[0];
            Console.WriteLine(input + " : " + output);
        }
    }
}

DateTimes are stored as Ticks in the DB per default.

Expectation & Issue

My expectation would be that the Console.WriteLine will print the same values for both, input + output. But they are actually different and I'm not sure why. Sample line:

10.06.2015 00:53:03 : 09.06.2015 22:53:03

Question

What happened here? Why does SQLite.net-PCL not return the same date?

Guesswork

The only reason that I can think of but could not confirm is that SQLite does some corrections to the date in order to always store some UTC time (or so).

Aucun commentaire:

Enregistrer un commentaire