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:

```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()でエラーが出ない:

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


SQL文:

```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