views:

275

answers:

4

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?

A: 

I do it however it's easier, if it's easier to store data one way, I do it, regardless if it goes against what they prefer. What they prefer is what they want, it doesn't have to be what is best/easiest. If I were you just do it in whatever way is easier for you.

Matt S.
There are other criterias in our jobs besides ease-of-use.Performance, for example.
Slava Tutushkin
it really shouldn't matter, i think it's just for the human readibility factor, but I'm not 100% sure. SQLite doesn't do anything except store data, so it shouldn't matter what format it's in
Matt S.
It does matter when you need to do datetime math inside of the query.
Slava Tutushkin
+4  A: 

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.

Alex Martelli
So I will stick with the framework's defaults until I have explicit reasons not to do so.Thanks!
Slava Tutushkin
Could you please describe a little bit about why julianday's precision for hours, minutes and seconds is not good enough?
Slava Tutushkin
@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.
Alex Martelli
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.
dan04
+2  A: 

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.

Samuel Danielson
Thank you for this suggestion! It's a great idea and it solves all kind of my problems with this issue.
Slava Tutushkin
A: 

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.

dan04