概要

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 は行われず、小数部分はミリ秒として扱われていた。