MySQL の interval hour/minute 加減算で小数を与えると round される

概要

• MySQL の時刻計算で interval を使うと時刻の加減算ができる
  • 例: NOW() + interval 1 hour
• 0.4 hour 0.6 minute のように小数を与えると、整数に四捨五入されて計算される
• second の場合は四捨五入されない

検証環境

• Amazon Aurora (5.6.mysql_aurora.1.19.5)
• MySQL 5.6.47

検証

2020-01-01 00:00:00 に小数の hour, minute, second を足してみる。

hour

MySQL> select '2020-01-01 00:00:00' + interval 0.4 hour;
+-------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 hour |
+-------------------------------------------+
| 2020-01-01 00:00:00                       |
+-------------------------------------------+
1 row in set (0.01 sec)

MySQL> select '2020-01-01 00:00:00' + interval 0.5 hour;
+-------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 hour |
+-------------------------------------------+
| 2020-01-01 01:00:00                       |
+-------------------------------------------+
1 row in set (0.01 sec)

• • 0.4 hour = + 0 hour
• • 0.5 hour = + 1 hour

minute

mysql> select '2020-01-01 00:00:00' + interval 0.4 minute;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 minute |
+---------------------------------------------+
| 2020-01-01 00:00:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select '2020-01-01 00:00:00' + interval 0.5 minute;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 minute |
+---------------------------------------------+
| 2020-01-01 00:01:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

• • 0.4 minute = + 0 minute
• • 0.5 minute = + 1 minute

second

mysql> select '2020-01-01 00:00:00' + interval 0.4 second;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 second |
+---------------------------------------------+
| 2020-01-01 00:00:00.400000                  |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select '2020-01-01 00:00:00' + interval 0.5 second;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 second |
+---------------------------------------------+
| 2020-01-01 00:00:00.500000                  |
+---------------------------------------------+

second の場合は round は行われず、小数部分はミリ秒として扱われていた。