My very first post to the Django Mailinglist was a question about the TIME_ZONE setting (and embarrassingly, from the look of that Google Groups page, the second one as well). Malcolm was nice enough to explain things, and that was that.

Until today, when I ran into another time zone related problem: A custom MySQL query (datetimefield > NOW()) worked fine on my local dev machine, but returned an incorrect result set on the server. Timezones always have me confused, annd even worse, it turned out I had already forgotten everything I learned back then. To make sure this won’t happen again, I decided to write up a quick summary of how the whole thing – apparently – works.

• The only thing Django’s TIME_ZONE setting is used for is setting the TZ environment variable (except when using PostgreSql, in which case the timezone is sent to the database as well).
• The value in TZ effects Python’s datetime and time modules, e.g. what is returned by datetime.datetime.today(). So throughout your Django application, your are probably going to work with dates from TIME_ZONE.
• Django doesn’t do anything else, but passes those date values directly on to MySQL.

What is MySQL doing? Apparently not that much either.

• The dates are stored, without any conversion or timezone information, as-is.
• When queried, they are returned as-is, unmodified, exactly as they were saved.
• The only exception is when dealing with timestamps, e.g. through a TIMESTAMP column. Because unix timestamps are in UTC, MySQL converts dates to UTC before storage based on a timezone setting, and does the reverse on retrieval.
• The same timezone setting also determines the output of functions such as NOW().

The last bullet means that comparing a Django DateTimeField with NOW() will only work if the timezone settings of Django and the MySQL server match. As it may be hard to ensure this (*), I would have to conclude that generating the timestamp in Python and putting it in the query, instead of using a MySQL function, is the right thing to do here.

In retrospect, it seems a lot less complicated than I assumed. TIME_ZONE determines what dates are generated, and from that point one, those values are fixed, and won’t change, regardless of environment, timezone etc. They have no timezone information attached, and you have to know how to interpret them. As long as you stick with one TIME_ZONE value, the dates in the database should always be consistent.

I’m sure I’ll be revisiting this entry soon enough.

(*) As MySQL supports a per-session time_zone setting, shouldn’t Django automatically set it to the right value?