On Sun, 2007-12-02 at 00:19 -0800, [EMAIL PROTECTED] wrote: > This ticket as been stuck in 'design decision needed' for a few months > now. I recently needed to solve this problem for a site I was > building, and came up with a workaround, so perhaps an account of my > approach can serve to get the 'design decision' ball rolling... > > In a nutshell, the problem is that MySQL can't handle timezone aware > datetimes. (Can any tell us if Oracle and SQLite do? I know PostgreSQL > does.) > > I can see two fundamentally different approaches to this problem: > > 1. Deal with it at the ORM level, perhaps by getting MySQL to emulate > timezone aware datetimes by using two dB fields - a naive datetime, > and a varchar containing the timezone info. > > 2. Convert all datetimes into UTC before they hit the dB, where they > are stored as naive datetimes.
Storing the datetime along with its related timezone would be my preference (a shame that it would require two fields in MySQL -- 1985 is calling and would like their non-features back). Converting things to UTC loses information. 2007-12-01T12:00-0600 and 2007-12-02T05:00+1100 are not the same pieces of information, even though they represent the same moment in the UTC timezone. Timezones supply extra information that we shouldn't wilfully discard. Whether that's how the default datetime field behaves or whether we need extra fields to handle timezone-aware (i.e. non-broken :-)) datetimes is a secondary issue, but it seems to crop up all over the place. > > While option #1 feels more architecturally elegant to me (since it > solves the problem at its source), the more I thought about it I > decided it wasn't very feasible. It would mean that common operations > like selecting objects by date or sorting objects by time can no > longer be implemented with an SQL command - which to my mind is a > major problem. Creating model fields that are stored in multiple (two or more) database columns is an enhancement we want to implement (Jacob and I have talked about it a couple of times in different contexts this week, so it's not something way out of left field). That would make this sort of thing easier on some databases. I want to hear what other people think to see which way we should go, although if I was a betting man, I'd guess we end up going with "convert to UTC" for the default and make fully aware datetime fields as an extra (whether in core or external is a minor issue). Malcolm --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~----------~----~----~----~------~----~------~--~---
