Rails ActiveRecordでdatetime型カラムのGROUP BY集計にタイムゾーンを考慮する

スポンサーリンク

Rails で ActiveRecord の group と count を使って、SQL の GROUP BY と COUNT による日付ごとのレコード集計を試みたところ一つ問題に遭遇。SQL の DATE 関数で datetime カラムを日付に変換して、ActiveRecord の group でナイーブに集計すると、DB に保存されたタイムゾーンでの日付ごとの集計となってしまいます。

— 環境 —
Rails 5.1
ActiveRecord 5.1
MySQL 5.6

前提として、Rails のタイムゾーン設定(Rails.application.config.time_zone)が UTC となっており、DB レコード保存の日時(datetime 型のカラム)が全て UTC で保存されているとします。

【お知らせ】 英単語を画像イメージで楽に暗記できる辞書サイトを作りました。英語学習中の方は、ぜひご利用ください!
画像付き英語辞書 Imagict | 英単語をイメージで暗記
【開発記録】
英単語を画像イメージで暗記できる英語辞書サービスを作って公開しました
スポンサーリンク

普通にやると UTC ベースの日付ごとの集計になってしまう

例として、created_at カラムの日時を対象にして、日付ごとの Post 数を集計したい場合です。普通にモデルに scope を定義すると以下のような感じになるかと思います。

使う時はこうする。

ここで問題に遭遇。日本時間(JST)での日付ごとに集計したかったのですが、Post のデータは created_at が UTC で DB に保存されていますので、この scope のクエリで発行される GROUP BY は、UTC ベースでの日付ごとの集計を行っていました。

CONVERT_TZ 関数でタイムゾーンを考慮する

調べたところ、タイムゾーンを考慮した上で datetime 型のカラムを対象に、日付ごとの GROUP BY 集計を行いたい場合は、MySQL では CONVERT_TZ() 関数を使うと良いようです。

タイムゾーンを複数扱う場合は、’Asia/Tokyo’ の箇所を scope の引数にして、タイムゾーンの値を渡すようにすれば良いかと思います。これで…

とやって無事に、日本時間(JST)をベースにした日付ごとの集計になりました。

MySQL で CONVERT_TZ() 関数を使えるようにする

MySQL の環境によっては、CONVERT_TZ() 関数が使えない場合があります。MySQL にログインして確認する。

「select convert_tz(now(), ‘UTC’, ‘Asia/Tokyo’);」のクエリを発行して、上記のように NULL が返ってきた場合は CONVERT_TZ() が使えません。この場合、次のコマンドを実行して、タイムゾーン用の DB テーブルを作成する。

MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support

これは、上記 MySQL 公式ページに書いてあるのですが、自分の場合はこれだとちょっとエラーが出たので以下の方法で行った。

いったんタイムゾーン情報用の SQL をファイルに保存。

load の Warning は、不要なタイムゾーンまたは関係ないファイルであれば無視して良い。続いて、MySQL データベース(DB名 mysql)にインポートする。

以下あたりを参考にしました。

How to load MySQL time zone tables from Mac OS time zone files
MySQL Lists: mysql: Re: No database selected error when running mysql_tzinfo_to_sql

再度確認して、NULL が返らなければOKです。

以上の作業で、MySQL の CONVERT_TZ() 関数が使えるようになります。

スポンサーリンク
パーフェクト Ruby on Rails は、最近読んだ Rails 本の中では一番役に立った本です。Chef や Capistrano など Rails と共によく使用される技術にも触れてあります。Ruby on Rails 4 アプリケーションプログラミングは、入門的な内容で Rails の機能全体を網羅されています。
 
スポンサーリンク

Leave Your Message!