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

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

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

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

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

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

なので

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

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

# MySQL で timestamp 型の差分を出したい
## 【誤】減算

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

```sql
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型の時刻差を計算](http://d.hatena.ne.jp/sagra-da/20120409/1333991806)

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

確かに

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

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

```sql
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()](https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_timestampdiff)

```
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
```

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

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

### Result

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

```sql
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 より大きい」を取得するには更に変換が必要そう

```sql
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()
    - まどろっこしいですが使えそう
    - ただし、日付が古すぎる場合はうまく動かないそうです

```sql
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 には各種日付用の関数があるので、適切なものを使いましょう。