Search code examples
pythondatetimesqlitesqlalchemypysqlite

Shall I bother with storing DateTime data as julianday in SQLite?

SQLite docs specifies that the preferred format for storing datetime values in the DB is to use Julian Day (using built-in functions).

However, all frameworks I saw in python (pysqlite, SQLAlchemy) store the datetime.datetime values as ISO formatted strings. Why are they doing so?

I'm usually trying to adapt the frameworks to storing datetime as julianday, and it's quite painful. I started to doubt that is worth the efforts.

Please share your experience in this field with me. Does sticking with julianday make sense?

Solution

  • Store it both ways. Frameworks can be set in their ways and if yours is expecting to find a raw column with an ISO formatted string then that is probably more of a pain to get around than it's worth.

    The concern in having two columns is data consistency but sqlite should have everything you need to make it work. Version 3.3 has support for check constraints and triggers. Read up on date and time functions. You should be able to do what you need entirely in the database.

    CREATE TABLE Table1 (jd, isotime);

CREATE TRIGGER trigger_name_1 AFTER INSERT ON Table1
BEGIN
    UPDATE Table1 SET jd = julianday(isotime) WHERE rowid = last_insert_rowid();
END;

CREATE TRIGGER trigger_name_2 AFTER UPDATE OF isotime ON Table1
BEGIN
    UPDATE Table1 SET jd = julianday(isotime) WHERE rowid = old.rowid;
END;

    And if you cant do what you need within the DB you can write a C extension to perform the functionality you need. That way you wont need to touch the framework other than to load your extension.