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

Question

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?

Answer 1

Julian Day is handy for all sorts of date calculations, but it can's store the time part decently (with precise hours, minutes, and seconds). In the past I've used both Julian Day fields (for dates), and seconds-from-the-Epoch (for datetime instances), but only when I had specific needs for computation (of dates and respectively of times). The simplicity of ISO formatted dates and datetimes, I think, should make them the preferred choice, say about 97% of the time.

Answer 2

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.