Django, MySQL, Time zones and how to fix it
I just stumpled upon an error while customizing the Admin in a new Django project where I first used Django 1.6:
Database returned an invalid value in QuerySet.dates(). Are time zone definitions and pytz installed?
After using my favourite search engine to get an idea what this means and ensuring I had pytz installed, I didn’t find anything which solved my problem.
As the Django docs say, one should install pytz and ensure that the MySQL server has
the time_zone tables loaded with data.
So, actually on my MySQL server those tables were actually empty but on a Debian system it is enough to fill them with the script mysql_tzinfo_to_sql and pass
/usr/share/zoneinfo to it.
Then, I had many, many time_zone definitions in MySQL while I’m basically only interested in UTC as
this is what the server uses and also my application code.
Still, the error persisted. After trying to find the root cause of this in Django DB code without success, I had a quick look at the MySQL server configuration and the global “time_zone” server variable was set to “SYSTEM” while I expected it to read “UTC”.
So, for a quick’n’dirty test, I added a time zone name mapping for SYSTEM referring to the UTC time zone and guess what: it worked!
INSERT INTO `mysql`.`time_zone_name` VALUES ('SYSTEM', '588');
Though this felt more like a workaround instead a clean solution. So, I changed my
MySQL server configuration to set the time zone to UTC in general. To do this,
/etc/mysql/my.cnf and added the following line into the
default-time-zone = 'UTC'
Then restarted my MySQL server and now it everything works and the problem is solved in the proper way.