On Jun 1, 3:16 pm, VernonCole <vernondc...@gmail.com> wrote: > On the other hand, I found it necessary to convert date-time values to > ISO format strings in order to feed them to Microsoft ACCESS databases > in some cases, and that works well. But again, as with SQLite, the DB > has no concept of time zones. In the absence of actual time zone > support in the database, any action taken by django is going to > inconvenience somebody, and will likely not be compatible with non- > django use of the same database. Perhaps it would work to store the > pickled tz-aware datetime on brain-damaged databases. But, is it > reasonable to use an application-specific method to extend the > capability of a database engine?
The problem with storing pickled datetime objects is that they will most likely not be SQL-sortable. Consider the case of needing to sort a queryset by a timestamp field. This has to be achievable by the SQL backend, especially if it is a large dataset. Requiring Python to unpickle (or otherwise deserialize) a field for each row in a dataset, before doing a list sort, will not be efficient. It would also make it near impossible to do a "delete from foo where timestamp_field < '2011-01-01 12:34:56'" Whilst the idea of storing well formatted timestamp strings that include a UTC offset, it also fails when it comes to doing an SQL "order by". For example: "2011-06-03 01:01:00 +02" "2011-06-03 01:02:00 +08" "2011-06-03 01:03:00 +12" Using an ascending alphabetical sort, the timestamps will be listed in the order above. However, if we convert them to UTC, it becomes clear that the last value in the list is actually the earliest time, when normalized to UTC. "2011-06-03 01:01:00 +02" (2011-06-02 23:01:00 UTC) "2011-06-03 01:02:00 +08" (2011-06-02 17:02:00 UTC) "2011-06-03 01:03:00 +12" (2011-06-02 13:03:00 UTC) So whilst appending the UTC offset on the end is a convenient way of storing it in the same field as the timestamp itself, it leads to sorting errors. I think for this reason, the best option for SQLite may be to always store timestamps as UTC, and require Django to convert them to a local time (based on settings.TIME_ZONE perhaps as a default). That would make the timestamps in the above example sort correctly. That particular example is perhaps a bit extreme, with wildly different UTC offsets in the same table, but consider daylight saving transitions, where, for example, the offset may change from UTC +01 to UTC+02. That would mean that timestamps close to midnight could potentially be incorrectly sorted. Even worse, if you were grouping by a date_trunc function, grouping by month for example, you could end up with records being grouped into the wrong month. Then there is the fun and games when daylight saving finishes, and there is a one hour period where naive, local-TZ timestamps actually repeat, as wall clock jumps back one hour. At least with UTC timestamps, such phenomenon don't occur - same UTC+0 offset, all year round. -- 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.
