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".

SELECT @@time_zone;

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, I edited /etc/mysql/my.cnf and added the following line into the mysqld section:

default-time-zone = 'UTC'

Then restarted my MySQL server and now it everything works and the problem is solved in the proper way.

Comments

  • Gravatar for Bill PairaktaridisBill Pairaktaridis 4 years ago

    Nice guide. But you have an error in your syntax. It's default-time-zone='utc' with "-" instead of "_". :)

    Link / Reply
    • Gravatar for enricoenrico 4 years ago

      Oops :).

      Fixed, thank you.

      Link / Reply
  • Gravatar for Jernej JerinJernej Jerin 4 years ago

    Thanks for the heads up. But on Windows I had to write it like this: default-time-zone='+00:00'

    Link / Reply
  • Gravatar for YuraYura 3 years, 11 months ago

    You can also use something like
    -----------------------
    'OPTIONS': {
    'init_command': ("SET time_zone='%s'" % TIME_ZONE),
    },
    -----------------------
    in settings.py instead of changing global mysql conf.

    https://docs.djangoproject.com/en/1.6/ref/databases/#creating-your-tables

    Link / Reply
  • Gravatar for NishithNishith 1 year, 5 months ago

    Hi All,

    I am also struggling with the same issue. However even doing the above has not solved it. Somehow mysql installation on my windows 10 didn't even created the empty time zones tables. So somehow, I managed to create them using the queries from given link - https://github.com/google/mysql/blob/master/mysql-test/t/system_mysql_db_fix50030.test

    Thereafter, I loaded the tables from the sql link - https://dev.mysql.com/downloads/timezones.html

    and then changed the default-time-zone to '+00:00' somehow it is not taking in UTC. somehow still getting the error in django, what should I do to fix it?

    Link / Reply
Write a new Comment