MySQL DATETIME型のミリ秒の扱いについて

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

MySQL DATETIME型のミリ秒の扱いについて

MySQLのDATETIME型のカラムに対して、ミリ秒付きデータを書き込む際の動作について、

バージョンによって挙動が異なるので注意が必要です。

MySQL 5.5 までは、強制的に切り捨てされて格納、MySQL 5.6 以降は四捨五入されて格納されるという動作になっています。

実行例

■ [MySQL 5.5]

テーブル

mysql> show columns from hoges;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| data  | varchar(255) | YES  |     | NULL    |                |
| time  | datetime     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

書き込むデータ

mysql> SET @time1 = "2016-04-28 12:00:00.100";
mysql> SET @time2 = "2016-04-28 12:00:00.499";
mysql> SET @time3 = "2016-04-28 12:00:00.500";

mysql> SELECT @time1, @time2, @time3;
+-------------------------+-------------------------+-------------------------+
| @time1                  | @time2                  | @time3                  |
+-------------------------+-------------------------+-------------------------+
| 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00.500 |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

データ格納結果

mysql> INSERT INTO hoges (data, time) VALUES (@time1, @time1);
mysql> INSERT INTO hoges (data, time) VALUES (@time2, @time2);
mysql> INSERT INTO hoges (data, time) VALUES (@time3, @time3);

mysql> select * from hoges;
+----+-------------------------+---------------------+
| id | data                    | time                |
+----+-------------------------+---------------------+
|  3 | 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00 |
|  4 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00 |
|  5 | 2016-04-28 12:00:00.500 | 2016-04-28 12:00:00 |
+----+-------------------------+---------------------+

■ [MySQL 5.7]

テーブル

mysql> show columns from hoge;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | MUL | NULL    | auto_increment |
| data  | varchar(255) | YES  |     | NULL    |                |
| time  | datetime     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

書き込むデータ

mysql> SET @time1 = "2016-04-28 12:00:00.100";
mysql> SET @time2 = "2016-04-28 12:00:00.499";
mysql> SET @time3 = "2016-04-28 12:00:00.500";

mysql> SELECT @time1, @time2, @time3;
+-------------------------+-------------------------+-------------------------+
| @time1                  | @time2                  | @time3                  |
+-------------------------+-------------------------+-------------------------+
| 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00.500 |
+-------------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

データ格納結果

mysql> INSERT INTO hoge (data, time) VALUES (@time1, @time1);
mysql> INSERT INTO hoge (data, time) VALUES (@time2, @time2);
mysql> INSERT INTO hoge (data, time) VALUES (@time3, @time3);

mysql> select * from hoge;
+----+-------------------------+---------------------+
| id | data                    | time                |
+----+-------------------------+---------------------+
|  1 | 2016-04-28 12:00:00.100 | 2016-04-28 12:00:00 |
|  2 | 2016-04-28 12:00:00.499 | 2016-04-28 12:00:00 |
|  3 | 2016-04-28 12:00:00.500 | 2016-04-28 12:00:01 |
+----+-------------------------+---------------------+
3 rows in set (0.00 sec)

上記のように、同じデータを書き込んだ場合でも、結果が異なったものになっている。

■ おまけ

Railsでの動作について

RailsのActiveRecord経由で記録した場合、

Rails ~4.1だと、ミリ秒は切り捨てされて、

Rails 4.2~ だと、ミリ秒も含めてSQL文が作られる。