On Jun 28, 3:26 pm, Stephen Burrows <stephen.r.burr...@gmail.com> wrote: > I agree that it would be nice to be able to store tz-aware datetimes - > but if django were going to do so, it ought to store the datetimes > with the timezone information intact, rather than converting > everything to the project's timezone. So, if a conversion to UTC were > to take place, there would need to be a separate field in the database > to store the timezone.
As I've already illustrated earlier in this thread, storing the TZ with the timestamp in a DB that will treat it as a text string (such as SQLite) is going to break chronological SQL sorting. Perhaps contrary to popular misconception, Postgres does not store the TZ in "timestamp with time zone" fields. They are normalized to UTC and stored as Julian dates. The TZ is interpreted on input of a timestamp value, if the entered value is being specified in a TZ other than the client connection's current TZ. The TZ is also displayed in a SELECT of a timestamp field - but the TZ is that of the client connection, not that of the originally inserted value. Storing the TZ in a separate field is going to get pretty nasty for sorting as well, as the query will have to somehow concatenate or compute together the two fields before applying the sorting algorithm. I think the best way to handle multi-TZ timestamps is the way that Unix and other multi-user, multi-timezone OSes have been doing it for decades (mostly wrt to filesystems) - respect the TZ of the client inputting the data, normalize to a common TZ (usually UTC) and store as such. Perform all sorting and timestamp comparison/arithmetic in that single, normalized TZ. Upon output, render the value in the client's TZ. ...and that is essentially what Postgres does. -- 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.
