Teaching SQLite how to handle DateTimeOffset values #1

28. June 2017

Blog

, , , ,

Today, I’d like to present another real-life example for aspect-oriented programming and post-build reflection. The problem to be solved in a nutshell could be described as follows: How to store date / time values including timezone offset efficiently in an SQLite database?

Let’s look into the architecture in detail:

In a current project, I’m using a local SQLite database from within a .NET application (UWP Windows Store App). Fortunately, there exists the SQLite.Net-PCL library which makes basic access to the database from .NET code possible without manually writing SQL statements, assisted by SQLite-Net Extensions which builds upon the former and acts as (although very simple) OR mapper to the SQLite database.

The core problem lies within the limited range of data types supported by SQLite: If we omit null values, SQLite only understands text, numbers (integers and decimal numbers), and blob data. What about DateTime values, as commonly known from .NET? Well, by default SQLite.Net-PCL converts DateTime objects to .NET ticks (a tick corresponding to 100 nanoseconds) and stores this timestamp as integer value. If desired, SQLite.Net-PCL can be forced to store DateTimes as string (following ISO8601 format “YYYY-MM-DD HH:MM:SS.SSS”) by setting the storeDateTimeAsTicks parameter to false while creating the SQLiteConnection. (According to SQLite documentation, internal database functions can also handle date / time values as decimal numbers, but since the .NET helper library doesn’t utilize this functionality and it won’t help us in storing timezone offsets, we ignore it for the moment.)

The requirements:

Whenever the user may enter date / time values into the app, they are also asked for timezone information. The reason for this is that the full date / time information is then sent to a backend service to be reviewed by back-office personnel, and these users must see the timezone offset in addition to each date / time value. This means, we need to store timezone information in addition to the date and time values in the database.

The obvious solution is to use .NET DateTimeOffset objects within data model classes (which will be serialized to SQLite tables), as this simplifies date / time handling within the app since DateTimeOffset objects already contain the necessary timezone offset. Unfortunately, when writing DateTimeOffset objects to the database, SQLite.Net-PCL automatically converts them to UTC and stores them as ticks (or as ISO8601 string, as discussed above). This means, although the offset information is available throughout the app, it is lost as soon as a dataset it serialized to the database.

Potential solutions:

After some hours of brainstorming, I came up with the following approaches:

  1. Manually convert DateTimeOffset to string properties and store these, or
  2. extract the DateTime and offset (as TimeSpan date type) parts of the DateTimeOffset, and store these in two separate columns
  3. Store the DateTime and TimeSpan (offset) parts of the DateTimeOffset in two columns of a separate table, and maintain references to this separate table
  4. Use SQLiteNetExtensions’ TextBlob attribute to serialize the DateTimeOffset to a single text column
  5. Use string properties on data models (which will be converted to SQLite tables), and convert them to DateTimeOffset objects whenever they are used (e.g., in ViewModels) through AutoMapper or a similar library

Unfortunately, none of these is feasible for most real-life applications:

  • For #1 and #2, we’d need to add additional properties to the data model, flag the original DateTimeOffset properties with the SQLite.NET [Ignore] attribute, and handle manual conversion (in both directions) – the effort for these approaches rises with the number of data model classes / SQLite tables.
  • For #3, we’d need to define a custom data type (in order to specify how the DateTime and TimeSpan parts should be stored), and cannot use the default .NET DateTimeOffset type
  • For #4, we’d need to make sure that only SQLiteNetExtensions’ extension methods are used for DB insert / update, and we’d still need an additional string property on all the data model classes.
  • #5 looks most promising, but (as well as #1 and #4, by the way) it still stores values as string, which means we cannot perform mathematical operations (e.g. comparisons in order to sort data sets) on DateTime columns…

What are we gonna do?

Alright, let’s talk about approach #6! First, we’re going to define a .NET attribute that can be used to flag those DateTimeOffset objects in data model classes that should be stored including timezone offset, instead of converting them to UTC timestamps. In addition, we’ll need a separate process (in its simplest form, this is a .NET console application) that scans a given .NET assembly’s code, looks for properties flagged with the above-mentioned attribute, and adds some magic to the respective SQLite table class… For details, read on in part #2 of this tutorial!