Store DateTimeOffset in SQLite.Net

Question

In a WinRT (Windows 8.1 Store App) project, I'm using the SQLite.Net-PCL and SQLiteNetExtensions NuGet packages to store data in a local SQLite database file.

Several of my data models (aka tables) contain properties of type DateTimeOffset. The aim is to store these without losing the offset information. (Reason for this is that the user can enter timezone information while specifying dates / times, and this information must be stored in the database.)

I'm aware of the storeDateTimeAsTicks parameter that can be set while creating the SQLiteConnection, setting this to false forces all DateTime properties to be stored as text in ISO format - however, this has no consequences on DateTimeOffset properties, as these are always automatically converted to UTC and stored as a number representing ticks.

I can think of the following 4 approaches:

• Manually convert DateTimeOffset to string properties and store these, or
• extract the DateTime and offset (as TimeSpan date type) parts of the DateTimeOffset, and store these in two separate columns,

=> but for both approaches, I'll need to add additional properties to the data model, flag the original DateTimeOffset properties with the [Ignore] attribute, and handle manual conversion (in both directions) - since I'd need to apply this to a lot of different data model classes, it seems too hard to maintain.

• Store the DateTime and TimeSpan (offset) parts of the DateTimeOffset in two columns of a separate table, and maintain references to this separate table

=> but in this case I'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

• Use SQLiteNetExtensions' TextBlob attribute to somehow serialize the DateTimeOffset to a single text column

=> but this feels somewhat hacky, I'd need to make sure that only SQLiteNetExtensions' extension methods are used for DB insert / update, and I'd still need an additional string property on all the data model classes...

So, my question is: Is there a more straigtforward, obvious solution that I'm missing?

Shepperd

I haven't successfully tested it, but have you tried using the SQLite.Net.DateTimeOffset nuGet package? Usage documentation is here: github.com/mobilemotion/SQLite.Net.DateTimeOffset – Citarella

Thanks @Citarella - I wrote and published that Nuget library myself, since nobody could answer my question. I should probably post an answer to the question as soon as I have some more time... – Shepperd

Side note: DateTimeOffset is almost never the same as DateTime + "user timezone". In fact, the part most people care about is the timezone, or often the behavior surrounding DST (with the offset being a secondary consideration). If you're storing absolute, always maps to the same instant, times, then the actual zone is somewhat unimportant. If, on the other hand, any future date/times might reasonably change if the offset does (if DST is adjusted, usually), like for calendar/scheduling programs, storing the offset alone is unlikely to help completely. – Talithatalk

Answer 1

Since nobody came up with potential solutions, but the question still received some attention, I decided to report how I solved the problem:

Approach #1:

The very scenario that raised the original question included a mobile app consisting of

• API data model classes (used for serialization to / from JSON and upload to / download from the backend REST service),
• DB data model classes (representing SQLite tables), and
• various ViewModel classes used for the MVVM-style presentation layer

API models and DB models were nearly identical (except for attributes necessary for JSON serialization and SQLite OR mapping), the only structural difference being that properties representing dates / times were of type string in the API classes and DateTimeOffset in the DB classes. After downloading data from, and before uploading data to the backend, API and DB models were converted into each other using Automapper.

I simply removed the string to DateTimeOffset conversion from the Automapper configuration, and modified the DB data model classes so that DateTimeOffset values are represented as string, which implies that they are stored as formatted text in SQLite (fortunately, no date / time calculations on the DB layer were required). Since JSON objects received from the backend include timezone information, I could simply pass through those values to the DB models, thus ensuring that DB tables always contain dates / times as fully formatted datetime strings including timezone offset.

Conversion from string to DateTimeOffset now happens when creating ViewModel classes out of DB data models. Obviously, this happens more often than before (when converting API models to DB models) resulting in a little overhead, but I can live with that since I don't need to worry about the SQLite data type issue any more.

Approach #2:

Since approach #1 may not be applicable to all scenarios, I came up with an alternative solution that is based on the first one of the 4 potential solutions proposed in the original question, but with reduced manual effort:

I created a custom attribute [DateTimeOffsetSerialize] that can be assigned to DateTimeOffset properties in SQLite data model classes, and a postbuild task that decompiles the assembly after build has finished and scans all classes within the assembly to find those flagged properties. For each of these flagged properties, a duplicate property of type string is created automatically that contains the original property's serialized value, and this newly created string property will be used as SQLite table column (the original DateTimeOffset property is automatically flagged with the [Ignore] attribute).

This solution is available as NuGet package, and has been open-sourced on GitHub (the GitHub page also contains details usage instructions).

Shepperd