On Jun 5, 11:16 pm, Daniel Greenfeld <pyda...@gmail.com> wrote:
> If you store DateTime in another format then what the database is designed
> to deliver, then you don't just lose sorting and search capabilities, you
> also lose the ability for that data to be read and understood trivially by
> other tools besides Django.
The problem in the case of SQLite is that it is not _designed_ to
deliver any kind of timestamp. It simply gets stored as a completely
unintelligent ascii string. Sorting of such fields is alphanumeric,
not chronological, and any date/time functions in SQLite are
essentially doing a strptime() on the string before processing it. I
have no idea why the developers of SQLite chose to omit such an
important field type.
Julian dates are supported by many platforms, including SQLite:
sqlite> select julianday("2011-06-04 12:45:55");
2455717.03188657
...which can easily be stored in SQLite's REAL column type. An IEEE
float can give you about 1 µs resolution for Julian dates. And Julian
dates can be converted back to ISO timestamps:
sqlite> select datetime(2455717.03188657);
2011-06-04 12:45:55
Any DB client that did not have its own Julian date functions can
simply include the conversion in the select query, eg. "select col1,
col2, datetime(col3) from foo".
I realize it's a pretty tall order to expect Django to start storing
DateTime fields as Julian dates in SQLite, because of the backwards
compatibility implications. The cool thing is however, that SQLite's
datetime() function seems to handle a variety of input formats anyway:
sqlite> select datetime(2455717.03188657);
2011-06-04 12:45:55
sqlite> select datetime("2455717.03188657");
2011-06-04 12:45:55
sqlite> select datetime("2011-06-04 12:45:55");
2011-06-04 12:45:55
As a slight digression, I did some experimenting with Python's sqlite3
API to see how it would handle a non-naive datetime object. First, I
inserted a naive datetime, then two non-naive datetimes, two seconds
apart, but in different timezones. The unsorted order was:
sqlite> select * from test;
2011-06-05 00:20:02.788742
2011-06-05 00:20:02.788742+02:00
2011-06-04 17:20:04.793494-05:00
And here we can see the alphanumeric sorting incorrectly place the
chronologically-last timestamp at the start of the list:
sqlite> select * from test order by col1;
2011-06-04 17:20:04.793494-05:00
2011-06-05 00:20:02.788742
2011-06-05 00:20:02.788742+02:00
I think this clearly indicates that datetime objects *must* be
normalized to a single timezone for SQLite, and that timezone should
ideally be UTC. If all timestamps are in the same timezone, they'll at
least sort correctly (albeit still using an alphanumeric sort).
Alternatively, get true numeric/chronological sorting by using a REAL
column type and storing Julian dates. It'll be more storage-efficient
than an ascii string too.
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com.
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
