Re: Timezone aware datetimes and MySQL (ticket #5304)
On 12/2/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > 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. I'm doing something similar using a custom field, which saves the local and UTC times and the timezone info: http://www.djangosnippets.org/snippets/388/ I have not yet had any problems with it, and I like having bot the local time for display purposes, and the UTC time for sorting. Ludo --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
Re: Timezone aware datetimes and MySQL (ticket #5304)
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 -~--~~~~--~~--~--~---
Re: Timezone aware datetimes and MySQL (ticket #5304)
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 -~--~~~~--~~--~--~---
Timezone aware datetimes and MySQL (ticket #5304)
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. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
