Django: Database returned an invalid value in QuerySet.datetimes(). Are time zone definitions for your database and pytz installed?

pytz をインストール:

$ pip install pytz

MySQLにデフォルトタイムゾーンを指定:

$ sudo vim /etc/mysql/my.cnf

[mysqld]
default-time-zone = "+09:00"

タイムゾーン情報をMySQLに設定:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

これで解消。

ちなみに、

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | grep "^INSERT" | awk '{ print $3 }' | sort | uniq |  ¥
  while read table; do echo "=== $table ===" ; echo "desc mysql.$table" |  ¥
  mysql -u root --password=$DBROOT_PASSWD -t ; done

Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

=== time_zone ===

+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Use_leap_seconds | enum('Y','N')    | NO   |     | N       |                |
+------------------+------------------+------+-----+---------+----------------+

=== time_zone_name ===

+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| Name         | char(64)         | NO   | PRI | NULL    |       |
| Time_zone_id | int(10) unsigned | NO   |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+

=== time_zone_transition ===

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| Time_zone_id       | int(10) unsigned | NO   | PRI | NULL    |       |
| Transition_time    | bigint(20)       | NO   | PRI | NULL    |       |
| Transition_type_id | int(10) unsigned | NO   |     | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+

=== time_zone_transition_type ===

+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| Time_zone_id       | int(10) unsigned    | NO   | PRI | NULL    |       |
| Transition_type_id | int(10) unsigned    | NO   | PRI | NULL    |       |
| Offset             | int(11)             | NO   |     | 0       |       |
| Is_DST             | tinyint(3) unsigned | NO   |     | 0       |       |
| Abbreviation       | char(8)             | NO   |     |         |       |
+--------------------+---------------------+------+-----+---------+-------+

django/db/models/query.py:

    def datetimes(self, field_name, kind, order='ASC', tzinfo=None):
        """
        Returns a list of datetime objects representing all available
        datetimes for the given field_name, scoped to 'kind'.
        """
        assert kind in ("year", "month", "day", "hour", "minute", "second"), \
                "'kind' must be one of 'year', 'month', 'day', 'hour', 'minute' or 'second'."
        assert order in ('ASC', 'DESC'), \
                "'order' must be either 'ASC' or 'DESC'."
        if settings.USE_TZ:
            if tzinfo is None:
                tzinfo = timezone.get_current_timezone()
        else:
            tzinfo = None 
        return self._clone(klass=DateTimeQuerySet, setup=True,
                _field_name=field_name, _kind=kind, _order=order, _tzinfo=tzinfo)

対処したらdatetimes()でエラーが出ない:

    >>> Profile.objects.datetimes('created_at','year')
    [datetime.datetime(2014, 1, 1, 0, 0, tzinfo=<DstTzInfo 'Asia/Tokyo' JST+9:00:00 STD>)]

SQL文:

    SELECT DISTINCT 
    CAST(
        DATE_FORMAT(
            CONVERT_TZ(`closed_profile`.`created_at`, 'UTC', 'Asia/Tokyo'), 
            '%Y-01-01 00:00:00') AS DATETIME
    ) 
    FROM `closed_profile` ORDER BY 1 ASC LIMIT 21                

CONVERT_TZ()を対処していない環境で実行するとNULLが返る：

$ echo "select CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo') from closed_profile limit 1" | python manage.py dbshell

CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo')
NULL

TZ情報を流し込む：

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root --password=$DBROOT_PASSWD  mysql 
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

$ echo "select CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo') from closed_profile limit 1" | python manage.py dbshell

CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo')
NULL

リスタート必要:

$ sudo /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld . ..
Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

時刻が変換される:

$ echo "select CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo') from closed_profile limit 1" | python manage.py dbshell

CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo')
2014-04-28 22:32:46