- 更新日: 2017年6月16日
- Rails
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 を定義すると以下のような感じになるかと思います。
|
1 2 3 4 5 |
class Post < ApplicationRecord belongs_to :user scope :count_grouped_by_date, -> { group("DATE(created_at)").count } # ... end |
使う時はこうする。
|
1 |
user.posts.count_grouped_by_date |
ここで問題に遭遇。日本時間（JST）での日付ごとに集計したかったのですが、Post のデータは created_at が UTC で DB に保存されていますので、この scope のクエリで発行される GROUP BY は、UTC ベースでの日付ごとの集計を行っていました。
CONVERT_TZ 関数でタイムゾーンを考慮する
調べたところ、タイムゾーンを考慮した上で datetime 型のカラムを対象に、日付ごとの GROUP BY 集計を行いたい場合は、MySQL では CONVERT_TZ() 関数を使うと良いようです。
|
1 2 3 4 5 6 7 |
class Post < ApplicationRecord belongs_to :user scope :count_grouped_by_date, -> { group("DATE(CONVERT_TZ(created_at, 'UTC', 'Asia/Tokyo'))").count } # ... end |
タイムゾーンを複数扱う場合は、’Asia/Tokyo’ の箇所を scope の引数にして、タイムゾーンの値を渡すようにすれば良いかと思います。これで…
|
1 |
user.posts.count_grouped_by_date |
とやって無事に、日本時間（JST）をベースにした日付ごとの集計になりました。
MySQL で CONVERT_TZ() 関数を使えるようにする
MySQL の環境によっては、CONVERT_TZ() 関数が使えない場合があります。MySQL にログインして確認する。
|
1 2 3 4 5 6 7 8 9 |
mysql> select convert_tz(now(), 'UTC', 'Asia/Tokyo'); +----------------------------------------+ | convert_tz(now(), 'UTC', 'Asia/Tokyo') | +----------------------------------------+ | NULL | +----------------------------------------+ 1 row in set (0.00 sec) |
「select convert_tz(now(), ‘UTC’, ‘Asia/Tokyo’);」のクエリを発行して、上記のように NULL が返ってきた場合は CONVERT_TZ() が使えません。この場合、次のコマンドを実行して、タイムゾーン用の DB テーブルを作成する。
|
1 2 3 |
$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql |
MySQL :: MySQL 5.7 Reference Manual :: 10.6 MySQL Server Time Zone Support
これは、上記 MySQL 公式ページに書いてあるのですが、自分の場合はこれだとちょっとエラーが出たので以下の方法で行った。
いったんタイムゾーン情報用の SQL をファイルに保存。
|
1 2 3 4 5 6 |
$ mysql_tzinfo_to_sql /usr/share/zoneinfo > zone_import.sql Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it. 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. |
load の Warning は、不要なタイムゾーンまたは関係ないファイルであれば無視して良い。続いて、MySQL データベース（DB名 mysql）にインポートする。
|
1 2 3 |
$ mysql -D mysql -u root -p < ./zone_import.sql |
以下あたりを参考にしました。
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です。
|
1 2 3 4 5 6 7 8 9 |
mysql> select convert_tz(now(), 'UTC', 'Asia/Tokyo'); +----------------------------------------+ | convert_tz(now(), 'UTC', 'Asia/Tokyo') | +----------------------------------------+ | 2017-06-16 20:30:14 | +----------------------------------------+ 1 row in set (0.00 sec) |
以上の作業で、MySQL の CONVERT_TZ() 関数が使えるようになります。
- – 参考リンク –
- MySQL, Rails ActiveRecord date grouping and timezones – Stack Overflow
- mysqlのタイムゾーンがUTCでapplication.rbのconfig.time_zone=localの場合、date/datetimeをgroupするとタイムゾーンがUTC使われる件 – いろいろ作るよ
- Rails の関連記事
- RailsでMySQLパーティショニングのマイグレーション
- RailsプラグインGemの作成方法、RSpecテストまで含めたrails pluginの作り方
- RailsでAMPに対応するgemをリリースしました
- Railsでrequest.urlとrequest.original_urlの違い
- Railsでwheneverによるcronバッチ処理
- Google AnalyticsのRails Turbolinks対応
- Railsアプリにソーシャル・シェアボタンを簡単設置
- Rails監視ツール用にErrbitをHerokuで運用
- Facebook APIバージョンのアップグレード手順(Rails OmniAuth)
- window.NREUMがHTMLヘッダー部に自動挿入されるのはNew Relic用
Leave Your Message!