djangoのdatetime型に対して月や日で絞り込もうとすると上手く行かなったので解決方法のメモ.
不具合内容
class MyModel(models.Model):
created_at = models.datetime(verbose_name='作成日')
こんなモデルに対して
MyModel.objects.filter(created_at__year=2015)
は正しく動作するが,
MyModel.objects.filter(created_at__month=3)
や
MyModel.objects.filter(created_at__date=17)
は
どんなデータが入っていてもなぜか検索結果が0になった.
原因
DBがmysqlの場合に__month
や__date
をfilterに使用すると,内部的にはCONVERT_TZを使用してTZを考慮した日付に変換されるらしい.
MyModel.objects.filter(created_at__month=2).count()
とした場合に発行されるsqlは以下のようになる.
SELECT COUNT(*)
FROM `myapp_mymodel`
WHERE EXTRACT(MONTH FROM CONVERT_TZ(`myapp_mymodel`.`created_at`, 'UTC', 'Asia/Tokyo')) = 2)
この時,mysqlのtime_zoneテーブルに変換規則(timezone名や,標準時からどれくらいずれているかの情報)が入っていないとCONVERT_TZの戻り値がNULLになり,結果として検索条件に引っかからなくなる.
使っているmysqlで変換できるかを調べるには,mysqlサーバにログインして実際に以下のような変換クエリを投げてみるとわかる.
mysql> select CONVERT_TZ('2015-03-10 10:00:00', 'UTC', 'Asia/Tokyo');
正常だと以下のような感じ,情報が入っていないとNullが帰ってくる.
+--------------------------------------------------------+
| CONVERT_TZ('2015-03-10 10:00:00', 'UTC', 'Asia/Tokyo') |
+--------------------------------------------------------+
| 2015-03-10 19:00:00 |
+--------------------------------------------------------+
ちなみにMyModel.objects.filter(created_at__year=2015).count()
が上手く動くのは
__year
filterの場合はCONVERT_TZを使わずにBETWEENを投げているから.
SELECT COUNT(*)
FROM `myapp_mymodel`
WHERE `myapp_mymodel`.`created_at` BETWEEN '2014-12-31 15:00:00' AND '2015-12-31 14:59:59')
mysqlへの情報追加
mysqlに情報を追加するには以下のコマンドを実行すればいい.
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
バージョンによってmysql_tzinfo_to_sqlにバグ(?)があるらしく,
環境によっては以下のようなエラーメッセージが表示されることがある.
ERROR 1406 (22001) at line 39272: Data too long for column 'Abbreviation' at row 1
その場合はmysql_tzinfo_to_sqlを適当なファイル(ここではtzinfo.sql)に吐き出しておいて,該当箇所のsql文を無理やり書き換えて上げればひとまず動くようになる.
$ mysql_tzinfo_to_sql /usr/share/zoneinfo > tzinfo.sql
$ vim tzinfo.sql
Abbreviationの値を'Local time zone must be set--see zic manual page'
から'UNSET'
に変更する.
参考サイトによれば38982行目,自分の環境だと39273行目だった.
zic manual page
とかで検索を掛けたほうがいいかも.
変更前
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page');
↓
変更後
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES (@time_zone_id, 0, 0, 0, 'UNSET');
修正したsqlをmysqlに適用
$ cat tzinfo.sql | mysql -u root mysql
参考サイト
http://stackoverflow.com/questions/14454304/convert-tz-returns-null
http://stackoverflow.com/questions/17914284/mysql-convert-tz-command-returns-null