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

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.

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 Table1BEGIN    UPDATE Table1 SET jd = julianday(isotime) WHERE rowid = last_insert_rowid();END;CREATE TRIGGER trigger_name_2 AFTER UPDATE OF isotime ON Table1BEGIN    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.

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;};