MySQL で timestamp 型の差分を出すには

Posted at 2019-05-23

株式会社オズビジョンのユッコ (@terra_yucco) です。
今日はトラブル対応中に出くわした MySQL の小ネタ。

トラブルの内容

抽出ロジックにミスがあり、特定のアクションをしてから 60 分後までにはお知らせが飛ぶ予定だったのですが、それが一部の条件で飛ばなくなっていました。

対応自体は処理を修正して完了しましたが、影響を受けた人の抽出タスクが残ります。

2 つの日付の差分を取りたい

特定のアクションをしてから 60 分後までにはお知らせが飛ぶ予定

なので

特定のアクションをした日時
実際に対応後お知らせが飛んだ日時

がわかれば、取得は可能です。
幸い、この 2 つの値は timestamp 型で DB に入っていました。

MySQL で timestamp 型の差分を出したい

【誤】減算

timestamp 型は名称からして Unix の Epoch 秒なのかと思っていたので、安易に取ろうとしたのがこちら。

mysql> select user_id, register_date, notify_date, (notify_date - register_date) from actions where (notify_date - register_date) > 3600 limit 10;
+----------+---------------------+---------------------+-------------------------------+
| user_id  | register_date       | notify_date         | (notify_date - register_date) |
+----------+---------------------+---------------------+-------------------------------+
| 99999901 | 2019-05-23 19:35:07 | 2019-05-23 20:16:15 |                          8108 |
| 99999902 | 2019-05-23 19:35:08 | 2019-05-23 20:16:17 |                          8109 |
| 99999903 | 2019-05-23 19:40:07 | 2019-05-23 20:16:17 |                          7610 |
| 99999904 | 2019-05-23 19:40:08 | 2019-05-23 20:16:17 |                          7609 |
| 99999905 | 2019-05-23 19:40:09 | 2019-05-23 20:16:18 |                          7609 |
| 99999906 | 2019-05-23 19:40:09 | 2019-05-23 20:16:20 |                          7611 |
| 99999907 | 2019-05-23 19:40:11 | 2019-05-23 20:16:20 |                          7609 |
| 99999908 | 2019-05-23 19:45:09 | 2019-05-23 20:16:20 |                          7111 |
| 99999909 | 2019-05-23 19:45:10 | 2019-05-23 20:16:21 |                          7111 |
| 99999910 | 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                          6616 |
+----------+---------------------+---------------------+-------------------------------+
10 rows in set (0.00 sec)

What!?
一番下とかどう考えても 25 分くらいしか空いてないんですが、差分がすごいことに。

種明かし

回答はここにありました。

地中へのはしご > MySQLでDATETIME型の時刻差を計算

そのまま減算
数字列の差分になります。

確かに

[root@localhost ~]# php -a
Interactive shell

php > echo (20190523201623 - 20190523195007) . PHP_EOL;
6616

mysql> select register_date, notify_date, (notify_date - register_date) from actions where user_id = 99999910;

+---------------------+---------------------+-------------------------------+
| register_date       | notify_date         | (notify_date - register_date) |
+---------------------+---------------------+-------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                          6616 |
+---------------------+---------------------+-------------------------------+

TIMESTAMPDIFF()

今回こちらを使いました。

Synopsis

12.7 日付および時間関数 > TIMESTAMPDIFF()

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

atetime_expr2 − datetime_expr1 を返します。datetime_expr1 と datetime_expr2 は、日付または日付時間式です。式の一方が日付で、他方が日付時間にすることもできます。日付値は、必要に応じて時間部分が '00:00:00' の日付時間として処理されます。結果 (整数) の単位は、unit 引数で指定されます。

単純な減算とは、引数の指定が逆になります。

Result

これで期待する結果を取ることが出来ました！

mysql> select register_date, notify_date, timestampdiff(second, register_date, notify_date) from actions where user_id = 99999910;

+---------------------+---------------------+---------------------------------------------------+
| register_date       | notify_date         | timestampdiff(second, register_date, notify_date) |
+---------------------+---------------------+---------------------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                                              1576 |
+---------------------+---------------------+---------------------------------------------------+

その他の方法

使えなかったもの

DATEDIFF()
- 今回は日付ではなく分単位だったため
SUBTIME()
- 動きそうだと思ったけど NULL が返却された

使えそうなもの

TIMEDIFF()
- 以下のような結果なので使えそうだけど、結果から「60 より大きい」を取得するには更に変換が必要そう

mysql> select register_date, notify_date, timediff(notify_date, register_date) from actions where user_id = 99999910;

+---------------------+---------------------+--------------------------------------+
| register_date       | notify_date         | timediff(notify_date, register_date) |
+---------------------+---------------------+--------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 | 00:26:16                             |
+---------------------+---------------------+--------------------------------------+

TO_SECONDS()
- まどろっこしいですが使えそう
- ただし、日付が古すぎる場合はうまく動かないそうです

mysql> select register_date, notify_date, (to_seconds(notify_date) - to_seconds(register_date)) from actions where user_id = 99999910;

+---------------------+---------------------+-------------------------------------------------------+
| register_date       | notify_date         | (to_seconds(notify_date) - to_seconds(register_date)) |
+---------------------+---------------------+-------------------------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                                                  1576 |
+---------------------+---------------------+-------------------------------------------------------+

UNIX_TIMESTAMP()
- 私の実行系では TO_SECONDS() と同じ結果が得られました

Conclusion

timestamp 型のカラムを利用して日付の差分を取得したい場合は、そのまま減算しちゃダメ。
MySQL には各種日付用の関数があるので、適切なものを使いましょう。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up