Damir's Corner.

DateTime Values in SQLite When Using MvvmCross

In my spare time I'm developing an application in MvvmCross, using SQLite for local data storage. I'm taking advantage of MvvmCross SQLite-Net plugin. Recently I stumbled across a very strange behavior. The issue involved a fairly simple table with a DATETIME column:

CREATE TABLE [Session] (
  [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  [StartedAt] DATETIME NOT NULL,
  [ClosedAt] DATETIME,
  [Remark] NVARCHAR(100));

I had a corresponding model class in my code:

public class Session
{
  [PrimaryKey, AutoIncrement]
  public int Id { get; set; }
  public DateTime StartedAt { get; set; }
  public DateTime? ClosedAt { get; set; }
  public string Remark { get; set; }
}

I used the following code for inserting new records:

var session = new Session
{
  StartedAt = DateTime.Now
};
connection.Insert(session);

Re-reading the value from the database also behaved as expected:

var result = connection.Table<Session>.ToList()
                       .Single(s => s.Id == session.Id);
// no error here
Assert.AreEqual(session.StartedAt, result.StartedAt);

Everything seemed okay, until I tried to take a look at the table using SQLite Expert. This is how the above inserted record looked like:

Of course, this called for further investigation.

It turns out, SQLite's handling of DateTime values is quite strange. There is no separate storage class for DateTime values. Instead, they can be stored as TEXT, REAL or INTEGER values. Built-in date and time functions transparently support either one of those.

To make matters even more complicated, SQLite supports DATETIME data type inside CREATE TABLE statements. Such a column is assigned a NUMERIC type affinity which isn't a storage class, either. It can contain values of any supported storage class, but tries to convert the inserted text to INTEGER or REAL if it can be done in a lossless manner.

Now is the time to include SQLite-Net into the equation. This library supports storing of DateTime values either as TEXT or as INTEGER values. The mode is selected by the storeDateTimeAsTicks flag when creating a new SQLiteConnection. When creating a table with the library, the column data type is either DATETIME (for TEXT values) or BIGINT (for INTEGER values, i.e. ticks).

By default the library doesn't store DateTime values as ticks, which should still make everything work fine in my case. But here's, where the third player enters the stage: MvvmCross SQLite-Net plugin. It is actually a fork of SQLite-Net library with only minor changes to make it work easily with MvvmCross. But the one thing it does change, is the default storage mode for DateTime values: when using the plugin, by default they are stored as ticks, i.e. INTEGER values. This finally explains the strange behavior I observed: obviously SQLite Expert incorrectly interpreted the stored value since it didn't match its expectations based on the column data type.

I decided to avoid the issue by switching the DateTime storage mode in SQLite-Net back to TEXT. MvvmCross abstracts platform specifics by providing a different SQliteConnectionFactory for each one of them. These factories implement two interfaces: ISQLiteConnectionFactory and ISQLiteConnectionFactoryEx. Platform-specific application setup registers the correct factory which implements both factory interfaces.

Usually you'll obtain the correct factory in your view model by adding a constructor parameter of the required type. As long as you're satisfied with the default connection settings, you can use the ISQLiteConnectionFactory interface:

public abstract class ViewModelBase
{
  private readonly ISQLiteConnectionFactory _sqliteConnectionFactory;

  private string _filename = "db.sqlite";

  protected ViewModelBase(ISQLiteConnectionFactory sqliteConnectionFactory)
  {
    _sqliteConnectionFactory = sqliteConnectionFactory;
  }

  protected ISQLiteConnection CreateConnection()
  {
    return _sqliteConnectionFactory.Create(_filename);
  }
}

To change the default setting, ISQLiteConnectionFactoryEx interface needs to be used:

public abstract class ViewModelBase
{
  private readonly ISQLiteConnectionFactoryEx _sqliteConnectionFactory;

  private string _filename = "db.sqlite";

  protected ViewModelBase(ISQLiteConnectionFactoryEx sqliteConnectionFactory)
  {
    _sqliteConnectionFactory = sqliteConnectionFactory;
  }

  protected ISQLiteConnection CreateConnection()
  {
    return _sqliteConnectionFactory.CreateEx(_filename,
      new SQLiteConnectionOptions { StoreDateTimeAsTicks = false });
  }
}

No other code needs to be changed.

I chose TEXT storage class over INTEGER because of convenience. Having the column data type of DATETIME instead of BIGINT makes it much easier to work with data outside my application. This choice does have a disadvantage, though: less precision in stored values. In particular, the following test will now most likely fail:

var session = new Session
{
  StartedAt = DateTime.Now
};
connection.Insert(session);

var result = connection.Table<Session>.ToList()
                       .Single(s => s.Id == session.Id);
// will fail most of the time:
// milliseconds are stripped when stored to database
Assert.AreEqual(session.StartedAt, result.StartedAt);

In my case this wasn't an issue. Seconds are enough precise for me. You'll have to decide for yourself, which mode of storage is more suitable in your situation, though.