DateTime incorrect when added to SQLite DB through (Fluent)NHibernate

Question

I have a component that i want to store to an SQLite database.

public class Comp : Entity
{
    public virtual DateTime TimeStamp { get; set; }
    public virtual String Name { get; set; }
}

public class CompMap : ClassMap<Comp>
{
    public CompMap()
    {
        Id(x => x.Id);
        Map(x => x.TimeStamp);
        Map(x => x.Name);
    }
}

Nothing fancy really.

The problem is that the TimeStamp is incorrectly stored in the DB (SQLite-Explorer show the value '30-12-1899') I think it has something to do with the way nHibernate sends the DateTime to the database

NHibernate: INSERT INTO "Comp" (TimeStamp, Name) VALUES (@p0, @p1); select last_insert_rowid(); @p0 = 26.02.2010 10:08:09, @p1 = 'test1'

To me it looks like the DateTime is in a string format (might just be the .ShowSQL() command though) and SQLite can't handle the format (it's a german datetime formating) I tried to change the format using an IUserType but the result is still the same.

I've not found anyone else having this issue so I'm assuming the problem is in my code but I'm unable to find it.

Here's the testcode i use to initialize the DB and insert the value to the DB

using System;
using System.IO;
using ConsoleApplication1.db;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var sessionFactory = Fluently.Configure().Database(SQLiteConfiguration.Standard.UsingFile("test.db").ShowSql())
                    .Mappings(m => m.FluentMappings.AddFromAssembly(typeof(Comp).Assembly))
                    .ExposeConfiguration(config =>
                                             {
                                                 if (File.Exists("test.db"))
                                                 {
                                                     File.Delete("test.db");
                                                 }
                                                 new SchemaExport(config)
                                                   .Create(false, true);                                                 
                                             })
                    .BuildSessionFactory(); 
            var session = sessionFactory.OpenSession();
            var ts = DateTime.Now;
            Comp c = new Comp
                         {
                             Name = "test1",
                             TimeStamp = ts
                         };
            session.Save(c);
            session.Flush();
            session.Close();
        }
    }
}

When executing this command and checking the DB Content the TimeStamp doesn't have the current date/time but 1899-31-12

Answer 1

Did you try changing the current thread's culture to something else? InvariantCulture? If this would respect the locale that might result in a more usable format?

Answer 2

Do you know what type your TimeStamp is in the database? Sqlite has no specific DateTime type, and can store them as text, reals or ints. I don't know how to do it, but can you specify the column type for TimeStamp as text somehow.

If you ran that sql on your database, what would you get i.e.

INSERT INTO "Comp" (TimeStamp, Name) VALUES (26.02.2010 10:08:09, 'test1');

And then what would you get if you ran this:

INSERT INTO "Comp" (TimeStamp, Name) VALUES ('26.02.2010 10:08:09', 'test1');

It might depend on the column definition as to whether the later works or not.

Answer 3

Turns out it was indeed my fault plus a profound lack of doublechecking. I did write the datetime into the database and then checked the value in the database with sqlite explorer. Apparently that one has issues with DateTime fields and does present an incorrect value.

That, plus the original program did have a bug (the DateTime wasn't written properly.

My conclusion therefor is: Don't use sqlite explorer

LinqPad did correctly show the value and will thus be used from now on.