Reading DateTime from SQLite stored as unixepoch integer

2 posts, 0 answers
  1. Lars
    Lars avatar
    10 posts
    Member since:
    Oct 2015

    Posted 30 Nov 2015 Link to this post

    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?

  2. Thomas
    Thomas avatar
    588 posts

    Posted 01 Dec 2015 Link to this post

    Hi Lars,

    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....

    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top