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?

Tingaling

Answer 1

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.

Pithecanthropus

Thank you for this suggestion! It's a great idea and it solves all kind of my problems with this issue. – Tingaling

Why would you want to store it both ways? The only real advantage of storing Julian days over ISO-formatted strings is storage space. If your framework expects an ISO-formatted string, just use that. All of SQLite's datetime functions can process ISO strings just as well as julian days. – Tabulate

Since sqlite 3.31 you've been able to add generated virtual columns. So you could store the time as unixepoch in four bytes and have two virtual columns for julianday and string, should you happen to need them. The virtual columns could use decltypes that are converted to native types by your driver. – Overdraw

Answer 2

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.

Align

So I will stick with the framework's defaults until I have explicit reasons not to do so. Thanks! – Tingaling

Could you please describe a little bit about why julianday's precision for hours, minutes and seconds is not good enough? – Tingaling

@Slava, think about representing "1 second": that 1/86400.'th of a day, which CANNOT be precisely represented as the fractionary part of a double precision float because the latter's representation is in binary form and 86400 is not a power of two. – Align

Currently (between 1029-09-15 and 6771-07-07), the resolution of a Julian date stored as a double is 2**(-31) day ≅ 40.2 µs. – Fullbodied

Answer 3

But typically, the Human doesn't read directly from the database. Fractional time on a Julian Day is easily converted to human readible by (for example)

void hour_time(GenericDate *ConvertObject)
{
    double frac_time    = ConvertObject->jd;
    double hour         = (24.0*(frac_time - (int)frac_time));
    double minute       = 60.0*(hour - (int)hour);
    double second       = 60.0*(minute - (int)minute);
    double microsecond  = 1000000.0*(second - (int)second);

    ConvertObject->hour         = hour;
    ConvertObject->minute       = minute;
    ConvertObject->second       = second;
    ConvertObject->microsecond  = microsecond;

};

Judgemade

Answer 4

Because 2010-06-22 00:45:56 is far easier for a human to read than 2455369.5318981484. Text dates are great for doing ad-hoc queries in SQLiteSpy or SQLite Manager.

The main drawback, of course, is that text dates require 19 bytes instead of 8.

Fullbodied