Search code examples
sqliteiso8601julian-date

Julian Day to ISO 8601 string in SQLite

I have a table that stores date/time values as julian days in SQLite (using the julianday() function). I can't seem to figure out how to convert them back to ISO 8601 style strings (YYYY-mm-ddThh:m:ss.sss) when I read them?

Solution

  • Just feed the Julian day number to the datetime function:

    A time string can be in any of the following formats:
    [...]
    12. DDDDDDDDDD
    [...]
    Format 12 is the Julian day number expressed as a floating point value.

    So datetime(julianday_output) goes in the opposite direction as the julianday function:

    sqlite> select datetime(julianday(current_timestamp)) as dt_from_jd, current_timestamp as dt;
dt_from_jd          | dt
2011-09-30 14:46:52 | 2011-09-30 14:46:52