MySQL 5.6からDATETIMEは小数点以下が四捨五入(round)されるので気を付けよう

MySQL 5.6からTIME, DATETIME, TIMESTAMPがマイクロ秒精度をサポートしました。

MySQL 5.6 Reference Manual :: 11.3.6 Fractional Seconds in Time Values

で、ドキュメントにはちゃんとroundされるけどこれはSQL標準に従う挙動だからエラーとかワーニングは出ないよってさらっと書いてあるんですが、5.5では小数点以下は切り捨てだったんでマイクロ秒を付けてクエリ投げてた場合ハマりポイントになってます。

MySQL Bugs: #68760: Datetime rounding problem

ActiveRecordにおけるDATETIMEのマイクロ秒サポート

ここからはジョーカーさんのRails-4.2+MySQL-5.6での時刻オブジェクトのミリ秒の扱いについてへのアンサーエントリになるんですが、ActiveRecord 4.2では以下のPRがマージされたことでMySQLでもマイクロ秒を付けてクエリを送るようになりました(Rebuild: 56でmiyagawaさんがマージされるのに1年半ぐらいかかったって言ってたやつです)。

MySQL 5.6 Fractional Seconds by arthurnn · Pull Request #14359 · rails/rails

これは5.6のためにマイクロ秒付けるけど5.5には影響ないだろう(travis-ciはMySQL 5.5だからマイクロ秒のテストをCIで回せないというのがもともと1年半ぐらい放置されていたときの言い分だった)ということだったんだけど、roundされて切り上がると思った通りに動かないということをみんな甘くみていて、これ関係のissueがいくつか上がってきています。

queries with ActiveSupport::TimeZone have extra fractional time · Issue #17239 · rails/rails

小数点以下をそのカラムが持つマイクロ秒精度以上送ってroundされて切り上がるのは望ましくない挙動であることはコアチームの人も認識はしていて、精度に応じたフォーマットをするべきだよねとは言っています。

で、MySQLでは問題になってるけどPostgreSQLのほうはどうなってるのか、ちゃんと精度に応じたフォーマットがされているのかというと、PostgreSQLでも最大精度でマイクロ秒を送るので当然round問題は存在します。以下のテストはMySQLもPostgreSQLもdateがroundされた結果2014-08-17 12:30:01として保存されるのでFoo.find_by(created_at: date)でレコードがヒットしません。

    def test_formatting_datetime_according_to_precision
      ActiveRecord::Base.connection.create_table(:foos, force: true) do |t|
        t.datetime :created_at, precision: 0
        t.datetime :updated_at, precision: 4
      end
      date = ::Time.utc(2014, 8, 17, 12, 30, 0, 999999)
      Foo.create!(created_at: date, updated_at: date)
      assert foo = Foo.find_by(created_at: date)
      assert_equal date.to_s, foo.created_at.to_s
      assert_equal date.to_s, foo.updated_at.to_s
      assert_equal 000000, foo.created_at.usec
      assert_equal 999900, foo.updated_at.usec
    end

activerecord-mysql-awesome/test/cases/datetime_test.rb#L57-L69

PostgreSQLでこの挙動がこれまで問題とされてこなかったのは、精度を指定しなかった場合の挙動がMySQLとPostgreSQLでは異なるからです。MySQLでは精度を指定しない場合マイクロ秒を持たないとなりますが、PostgreSQLでは精度を指定しない場合マイクロ秒精度に制限を持たない(実質最大の精度を持つ)となるため、ふつうに使ってるひとがハマる機会があまりないということになります。

この問題は以前から認識していたので直す前段階として#17673をPRしてたけど空気的に4.2.0には入れてくれなさそうなんで4.2.0出たら直そうと思ってたんですが、ジョーカーさんがこの問題踏んだのでとりいそぎ修正PRを出しておきました。

Format the datetime string according to the precision of the datetime field. by kamipo · Pull Request #18067 · rails/rails

このまま4.2.0が出るとMySQL使ってるひと阿鼻叫喚だと思うんで上記PRをバックポートしたactiverecord-mysql-awesome 0.0.2をリリースしておいたのでどうぞお納めください。これを使うと4.0, 4.1, まだ出てないけど4.2でマイクロ秒サポートの恩恵を受けることが出来ます。

activerecord-mysql-awesome 0.0.2