DATETIME型と現在時刻の差分が秒数でほしい時に"-"で比較してはいけない

TL;DR

• TIMESTAMPDIFF を使う
• 知ってたはずなのにやらかしたので自戒を込めてメモ

---

mysql80 65> CREATE TABLE t11 (dt DATETIME);
Query OK, 0 rows affected (0.14 sec)

mysql80 65> INSERT INTO t11 VALUES (NOW());
Query OK, 1 row affected (0.05 sec)

mysql80 65> SELECT * FROM t11;
+---------------------+
| dt                  |
+---------------------+
| 2023-05-04 06:23:00 |
+---------------------+
1 row in set (0.01 sec)

この時刻との差が(整数の)秒が欲しいからと言って、

mysql80 65> SELECT NOW(), dt, NOW() - dt FROM t11;
+---------------------+---------------------+------------+
| NOW()               | dt                  | NOW() - dt |
+---------------------+---------------------+------------+
| 2023-05-04 06:23:10 | 2023-05-04 06:23:00 |         10 |
+---------------------+---------------------+------------+
1 row in set (0.00 sec)

こうしてはいけない。

mysql80 65> SELECT NOW(), dt, NOW() - dt FROM t11;  -- 期待したのは70
+---------------------+---------------------+------------+
| NOW()               | dt                  | NOW() - dt |
+---------------------+---------------------+------------+
| 2023-05-04 06:24:10 | 2023-05-04 06:23:00 |        110 |
+---------------------+---------------------+------------+
1 row in set (0.01 sec)

NOW() - dt は CAST(NOW() AS SIGNED) - CAST(dt AS SIGNED) に変換されるので、 20230504062410 - 20230504062300 になるのでMySQL的には整数の110を返す。

基本、秒単位でしか離れないところで、分をまたいだ時だけ値がジャンプしてて変だなと思うまで思い出さなかった。反省。

正しくはTIMESTAMPDIFFでこう（名前からして、引数をUNIXTIMEにしないといけないかと思って雑にやったのが良くない…）

mysql80 65> SELECT NOW(), dt, NOW() - dt, TIMESTAMPDIFF(SECOND, dt, NOW()) FROM t11;
+---------------------+---------------------+------------+----------------------------------+
| NOW()               | dt                  | NOW() - dt | TIMESTAMPDIFF(SECOND, dt, NOW()) |
+---------------------+---------------------+------------+----------------------------------+
| 2023-05-04 06:26:51 | 2023-05-04 06:23:00 |        351 |                              231 |
+---------------------+---------------------+------------+----------------------------------+
1 row in set (0.00 sec)

​