メインコンテンツまでスキップ

MySQL の時刻の四捨五入

· 約5分
tanzhuo

MySQL の DATETIME はデフォルトで秒までの精度ですが、Java の LocalDateTime はナノ秒まで扱えます。ミリ秒付きの時刻を MySQL に書き込むと、カラムの精度を超えた部分は四捨五入され、保存された時刻がどこからともなく 1 秒増えることがあります。

問題の現象​

例えば、Java 側の時刻が 2022-01-01 12:34:56.789 のとき、これを通常の DATETIME カラムに書き込んで読み出すと 2022-01-01 12:34:57 になっています。ミリ秒部分が四捨五入されて繰り上がったのです。

このズレは普段は目立ちませんが、いくつかの場面で表面化します：

  1. 単体テストでエンティティを保存して読み出し、assertEquals で時刻フィールドを比較すると、たまに失敗する。ミリ秒が 500 以上のときだけ繰り上がるため、問題が出たり出なかったりします。

  2. 作成日時をカーソルページネーションや範囲クエリに使うと、境界のレコードが 1 件重複したり漏れたりする。

  3. ログの時刻と DB 内の時刻が 1 秒ずれ、トレースを追うときに突き合わせが合わない。

原理の概要​

MySQL は 5.6.4 以降、DATETIME、TIMESTAMP、TIME のいずれも小数秒をサポートしています。精度は型定義の fsp（fractional seconds precision）で決まり、値は 0 から 6、デフォルトは 0、つまり秒までです。

鍵となる挙動はこうです。挿入値の精度がカラムの精度より高い場合、MySQL は SQL 標準に従って切り捨てではなく四捨五入を行い、しかもデフォルトでは何の警告も出しません。そのため .789 は秒の位を繰り上げ、.400 は黙って捨てられます。どちらの結果も Java のメモリ上の値とは一致しません。

Java 側では、LocalDateTime は内部的にナノ秒で時刻を保持しており、JDBC ドライバは書き込み時に小数秒も一緒にサーバーへ送ります。つまり丸めは MySQL 側で発生します。java.sql.Timestamp も同じくナノ秒精度なので、Java の型をこれに替えるだけではデータベース側の丸めは避けられません。問題の根はカラムの精度定義にあるのです。

解決策​

1) カラムの精度を上げる​

ビジネス上ミリ秒精度が必要なら（作成・更新時刻を記録する大半のケースはこれで十分）、カラムを DATETIME(3) として定義します：

ALTER TABLE t_order
  MODIFY create_time DATETIME(3) NOT NULL;

マイクロ秒が必要なら DATETIME(6) を使います。対になるデフォルト値の関数にも精度を付ける必要がある点に注意してください。NOW() は秒までなので、NOW(3) または CURRENT_TIMESTAMP(3) と書かなければ、デフォルト値自体に小数秒が付きません。

2) Java 側で事前に切り捨てる​

テーブル構造を変えたくない場合は、書き込み前に精度を秒に揃えます：

// 秒未満の部分を捨てて、DATETIME(0) の精度に揃える
LocalDateTime now = LocalDateTime.now().truncatedTo(ChronoUnit.SECONDS);

こうすればメモリ上の値と DB に保存される値が完全に一致し、比較やアサーションでズレが出ることはなくなります。共通のエンティティ基底クラスや監査フィールドの設定処理で一度だけ統一的に処理すれば十分です。

ハマりどころと注意点​

  • 丸めは「繰り上げ」であって「ゼロ埋め切り捨て」ではありません。余分な精度は切り捨てられると無意識に思い込んでいる人が多いのですが、実際は四捨五入されます。これこそが「時刻が 1 秒増える」という奇妙な問題の出どころです。MySQL 8.0 には TIME_TRUNCATE_FRACTIONAL という sql_mode があり、挙動を切り捨てに変えられますが、これはグローバルな挙動に影響するため、変更前に既存業務への影響を評価する必要があります。
  • DATETIME(3) に変更した後、既存データの小数秒はすべて .000 です。新旧データを混ぜてソートする際はこの点に注意してください。
  • fsp が高いほどストレージ消費も増えます。必要に応じて選べばよく、一律に DATETIME(6) を使う必要はありません。

まとめ​

時刻が「1 秒増える」ことの本質は精度の不一致です。Java はナノ秒を渡すのに、MySQL のカラムは秒までしか保存できず、はみ出した部分が四捨五入されます。解決の道は 2 つ。カラムを DATETIME(3)/DATETIME(6) に変えてデータベース側で保存できるようにするか、Java 側で truncatedTo(ChronoUnit.SECONDS) を使って事前に揃えるかです。原則はただ一つ、メモリ上の精度とカラム定義の精度を一致させること。そうすれば、書き込んだものがそのまま読み出せます。

COMMENTS