On 12/2/07, [EMAIL PROTECTED] <[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. > > 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. > > Option #2 feels a bit icky, but is much easier to implement, and it's > actually what I ended up using in the workaround I developed. > > I ended up writing a custom save method which converts timezone aware > datetimes to UTC then strips off the timezone data before storing > them. If the datetime is naive, it assumes it's in the timezone set in > settings.py, and then converts it to UTC. I call these datetimes 'sly' > datetimes - they're neither 'aware' nor completely 'naive', but they > know more than they appear to :-) > > With this approach, since I know that everything in the database is > UTC, I can do reliable ordering via SQL. There's still a bit of > jiggery-pokery needed when I have to do things like select items from > a particular day in a specific timezone (I have to figure out when the > day starts and ends in UTC), but it all works OK. I've also got > template filters that can convert to the viewer's timezone. > > Are there any opinions on this approach? > > If this general approach finds acceptance I could start turning my > workaround code into a more robust and generalised patch.
Did you consider using MySQL's TIMESTAMP type ( http://dev.mysql.com/doc/refman/5.0/en/timestamp.html)? Seems it would let you push all of the work of conversion to UTC down into the db backend and the MySQL server. For saving values, the db backend could use the timezone information in the datetime value to set the time zone for the connection, then the MySQL server would convert to UTC for storage. (The per-connection timezone specification does require MySQL 4.1 or higher.) For retrieval, I'm not sure how you'd communicate the desired timezone to the db backend, but I'd think it could be done sensibly somehow with a little thought. Karen --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
