Store DateTimeOffset in SQLite.Net

Question

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

Some of my data models (aka tables) contain properties of type DateTimeOffset . The goal is to store them without loss of bias information. (The reason for this is that the user can enter time zone information when specifying dates / times, and this information should be stored in a database.)

I know the storeDateTimeAsTicks parameter, which can be set when creating SQLiteConnection , setting it to false , so that all DateTime properties are saved as ISO text, but this has no effect on DateTimeOffset properties, since they are always automatically converted to UTC and saved as a number representing tics.

I can think of the following 4 approaches:

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

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

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

=>, but in this case I will need to define a custom data type (to indicate how the parts of DateTime and TimeSpan should be stored), and cannot use the .NET type DateTimeOffset by default

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

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

So my question is: Is there a more complex, obvious solution that I am missing?

Answer 1

Since no one came up with potential solutions, but the question still got some attention, I decided to let you know how I solved the problem:

Approach No. 1:

The script itself, which raised the original question, included a mobile application consisting of

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

The API models and DB models were almost identical (with the exception of the attributes necessary for JSON serialization and SQLite OR collation), the only structural difference is that the properties representing date / time were of type string in the API classes and DateTimeOffset in the DB classes. After loading the data and before loading the data onto the backend, the API and database models were converted into each other using Automapper.

I simply removed the string to DateTimeOffset conversion from the Automapper configuration and modified the database data model classes so that DateTimeOffset values ​​are represented as string , which means they are stored as formatted text in SQLite (fortunately, no date and time calculations at the database level were required) . Since JSON objects retrieved from the backend include time zone information, I can simply pass these values ​​to the database model, thereby ensuring that the database tables always contain dates / times as fully formatted time and time strings, including time zone offset.

Conversion from string to DateTimeOffset now occurs when creating ViewModel classes from database data models. Obviously, this happens more often than before (when converting API models to database models), which leads to a little overhead, but I can live with it because I no longer need to worry about a SQLite data type problem.

Approach No. 2:

Since approach No. 1 is not applicable to all scenarios, I came up with an alternative solution based on the first of 4 possible solutions proposed in the original question, but with reduced manual effort:

I created a custom attribute [DateTimeOffsetSerialize] , which can be assigned to DateTimeOffset properties in SQLite data model classes, and a post-assembly task that decompiles the assembly after the assembly is completed, and scans all the classes in the assembly to find these marked properties, for each of these of the marked properties, a duplicate property of type string is automatically created that contains the original value of the serialized property, and this newly created string property will be used as a column of the SQLite table (the original DateTimeOffset property of the tagged with the [Ignore] attribute).

This solution is available as a NuGet package and is open source on GitHub (the GitHub page also contains detailed usage instructions).