MySQLのdatetime型では、小数点以下の秒数を四捨五入する

こんにちは。エキサイト株式会社の三浦です。

MySQLで日時データを扱う際、datetime型を使うことが多いでしょう。

便利な型ですが、小数点以下の秒数を入れる際に少し注意点があることを発見したので、今回はそれについて説明します。

MySQLとdatetime型
MySQLのdatetime型と小数点以下の秒数
小数点以下の秒数がある場合の対応策
- 対応策１：小数点以下の秒数をあらかじめ処理しておく
- 対応策２：小数点以下の秒数までデータを含めるようにする
最後に

MySQLとdatetime型

MySQLで日時を扱う際は、多くの場合datetime型を使うことになると思います。

データの入れ方も非常に簡単です。

今回は、Javaでデータを入れてみます。

// 現在日時を取得
LocalDateTime now = LocalDateTime.now();

// 確認用に、Javaのログに現在日時を表示する
System.out.println(now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

// sampleTableにレコードを追加
sampleTable.insertSelective(
        sampleRecord.withCreatedAt(now)
);

MyBatisというライブラリのクエリビルダを使っていますが、内容としては「現在日時をsampleTableのレコードとして入れる」というものです。

これを実行すると、以下のようになります。

Javaのログ

MySQL上のデータ

想定通りのデータを、簡単に入れることが出来ました。

ですが、たまにこのようなパターンも起こります。

Javaのログ

MySQL上のデータ

1秒ですが、ずれてしまっているのです。

これはどうしたことでしょうか。

MySQLのdatetime型と小数点以下の秒数

先程のJavaのコードで、もう少し厳密に日時を表示してみます。

LocalDateTime now = LocalDateTime.now();

System.out.println(now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));

// 小数点以下の秒数もログに表示する
System.out.println(now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS")));

sampleTable.insertSelective(
        sampleRecord.withCreatedAt(now)
);

すると、以下のような結果が得られました。

Javaのログ

MySQL上のデータ

このデータから分かる通り、実はMySQLのdatetime型では、小数点以下の秒数は四捨五入するようになっています。

よく見てみると、MySQLのドキュメントにもその記述が存在します。

dev.mysql.com

小数秒部分を持つ TIME、DATE、または TIMESTAMP 値を同じ型のカラムに挿入するが、小数部の桁数が少ない場合、次の例に示すように丸めが行われます。

四捨五入されても問題ないデータであればいいでしょうが、そうされてしまうと問題がある（例えば、必ず切り捨ててほしいなどの）場合はどうすれば良いでしょうか？

小数点以下の秒数がある場合の対応策

対応策は、大きく2つあると思われます。

対応策１：小数点以下の秒数をあらかじめ処理しておく

1つ目の対応策は、MySQLにデータを入れる前にあらかじめ小数点以下の秒数を処理しておくことです。

例えばJavaでは、以下のようにすることで小数点以下の秒数を取り除くことが出来ます。

LocalDateTime now = LocalDateTime.now();

System.out.println(now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS")));

// 整数秒未満の日時データを取り除く
LocalDateTime truncatedNow = now.truncatedTo(ChronoUnit.SECONDS);

System.out.println(truncatedNow.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSSSSSSSS")));

sampleTable.insertSelective(
        sampleRecord.withCreatedAt(truncatedNow)
);

結果、以下のようになります。

Javaのログ

MySQL上のデータ

四捨五入ではなく、切り捨てでMySQLにデータを入れることが出来ました。

対応策２：小数点以下の秒数までデータを含めるようにする

もう一つは、datetime型をカラムに指定する時に、小数点以下の秒数まで含めるよう設定することです。

dev.mysql.com

小数秒部を含むカラムを定義するには、type_name(fsp) の構文を使用します。ここで、type_name は TIME、DATETIME、または TIMESTAMP であり、fsp は小数秒の精度です。例:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

一定の小数点以下の秒数まで入っていれば問題ない仕様なのであれば、これでも良いでしょう。

最後に

例えばJavaのように、現在日時をアプリケーションコードで取得すると自動的に小数点以下の秒数まで取得するような言語の場合、そのままMySQLにデータを入れることで思わぬズレが生じてしまうこともあります。

バグを生み出してしまわないよう、気をつけてデータを取り扱っていきましょう。