Timezone aware datetimes and MySQL (ticket #5304)
ether.mu...@gmail.com
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.
Karen Tracey
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
Malcolm Tredinnick
On Sun, 2007-12-02 at 00:19 -0800, ether.mu...@gmail.com 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
Ludovico Magnocavallo
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.
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