I'm trying to read with Telerik Data Access from a SQLite database which is filled natively. For performance reasons on both writing and reading apps, I'd prefer to store the timestamps as integer values, i.e. seconds since 1970-01-01 UTC = Unix epoch.
This works well when writing to SQLite natively and I can select the values either as integer values or correctly as ISO strings with
select datetime(MyTimestamp, 'unixepoch', 'utc') from MyTable
However, Telerik Data Access seems to want to force me to use string-based timestamps in the DB instead. The generated select statement looks like
select strftime('%Y-%m-%d %H:%M:%f',MyTimestamp) from MyTable
which then fails when trying to construct a DateTime object
System.ArgumentException: Unable to extract DateTime value from string '3962-47-18 12:00:00.000'.
Sure I can store ISO8601 timestamp strings in the DB instead, but that would require at least 3 expensive conversions:
1. format string when inserting into DB
2. format already preformatted string in DB again via strftime function call generated into SQL statement
3. parsing string to construct a DateTime object
all of which would be circumvented by using the integer value throughout.
How can I achieve this and still have a fluent mapping directly to a DateTime property and not resorting to a primitive int mapping and rolling my own code to wrap it into a DateTime?
Or is there any way to specify to use the modifier 'unixepoch' with strftime in the generated statement?
1 Answer, 1 is accepted
unfortunately, there is no easy option at the moment. In principle it is possible to write a custom AdoTypeConverter that performs the needed steps, but this is certainly not trivial to do. We might think of incorporating such a converter at a later point in time...
I think the best way to go at the moment is to use an integer field for that column and provide a DateTime property that just wraps it. As you already figured out....
Regards,
Thomas
Telerik