MySQLのテーブルカラムにcreated（データ作成日時）とmodified（データ更新日時）があるとして、トラブルシューティング調査時にその差分計算をしたいシーンがよくあります。他にもcreatedとNOW()の差分を出したい時もありますが、いずれの事例でもTIMESTAMPDIFF()という関数が便利でしたので今回紹介します。検証したMySQLバージョンは5.7.26です。

TIMESTAMPDIFF()を検証するためのデータ準備

とりあえず検証のためのデータを準備します。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

INSERT INTO
  users (id, name, created, modified) 
values 
  (1, 'aaa', '2020-01-01 00:00:00', '2020-01-01 00:00:15'),
  (2, 'bbb', '2020-01-01 00:00:00', '2020-01-01 00:00:59'),
  (3, 'ccc', '2020-01-01 00:00:00', '2020-01-01 00:01:00'),
  (4, 'ddd', '2020-01-01 00:00:00', '2020-01-01 00:01:01'),
  (5, 'eee', '2020-01-01 00:00:00', '2020-01-01 01:00:00')
;

TIMESTAMPDIFF()の結果検証

試しに日時差分を秒・分・時で算出してみます。

日時差分を秒単位で算出

SELECT
    *
    ,TIMESTAMPDIFF(SECOND, created, modified) sec_diff
FROM
    users
;

mysql> SELECT
    ->     *
    ->     ,TIMESTAMPDIFF(SECOND, created, modified) sec_diff
    -> FROM
    ->     users
    -> ;
+----+------+---------------------+---------------------+----------+
| id | name | created             | modified            | sec_diff |
+----+------+---------------------+---------------------+----------+
|  1 | aaa  | 2020-01-01 00:00:00 | 2020-01-01 00:00:15 |       15 |
|  2 | bbb  | 2020-01-01 00:00:00 | 2020-01-01 00:00:59 |       59 |
|  3 | ccc  | 2020-01-01 00:00:00 | 2020-01-01 00:01:00 |       60 |
|  4 | ddd  | 2020-01-01 00:00:00 | 2020-01-01 00:01:01 |       61 |
|  5 | eee  | 2020-01-01 00:00:00 | 2020-01-01 01:00:00 |     3600 |
+----+------+---------------------+---------------------+----------+
5 rows in set (0.00 sec)

日時差分を分単位で算出

SELECT
    *
    ,TIMESTAMPDIFF(MINUTE, created, modified) min_diff
FROM
    users
;

mysql> SELECT
    ->     *
    ->     ,TIMESTAMPDIFF(MINUTE, created, modified) min_diff
    -> FROM
    ->     users
    -> ;
+----+------+---------------------+---------------------+----------+
| id | name | created             | modified            | min_diff |
+----+------+---------------------+---------------------+----------+
|  1 | aaa  | 2020-01-01 00:00:00 | 2020-01-01 00:00:15 |        0 |
|  2 | bbb  | 2020-01-01 00:00:00 | 2020-01-01 00:00:59 |        0 |
|  3 | ccc  | 2020-01-01 00:00:00 | 2020-01-01 00:01:00 |        1 |
|  4 | ddd  | 2020-01-01 00:00:00 | 2020-01-01 00:01:01 |        1 |
|  5 | eee  | 2020-01-01 00:00:00 | 2020-01-01 01:00:00 |       60 |
+----+------+---------------------+---------------------+----------+
5 rows in set (0.00 sec)

日時差分を時単位で算出

SELECT
    *
    ,TIMESTAMPDIFF(HOUR, created, modified) hour_diff
FROM
    users
;

mysql> SELECT
    ->     *
    ->     ,TIMESTAMPDIFF(HOUR, created, modified) hour_diff
    -> FROM
    ->     users
    -> ;
+----+------+---------------------+---------------------+-----------+
| id | name | created             | modified            | hour_diff |
+----+------+---------------------+---------------------+-----------+
|  1 | aaa  | 2020-01-01 00:00:00 | 2020-01-01 00:00:15 |         0 |
|  2 | bbb  | 2020-01-01 00:00:00 | 2020-01-01 00:00:59 |         0 |
|  3 | ccc  | 2020-01-01 00:00:00 | 2020-01-01 00:01:00 |         0 |
|  4 | ddd  | 2020-01-01 00:00:00 | 2020-01-01 00:01:01 |         0 |
|  5 | eee  | 2020-01-01 00:00:00 | 2020-01-01 01:00:00 |         1 |
+----+------+---------------------+---------------------+-----------+
5 rows in set (0.00 sec)

TIMESTAMPDIFF()で他に使える単位

DAYやWEEK, MONTH, YEARなどがあります。マイクロ秒計算もいけるようです。

使い所の注意点

アプリケーションコード実装でも使えそうですが、算出を伴うSQLですのでレコード数が多い場合はCPUを少し食う・もしくは実行結果の返却に少し時間かかる等が考えられます。注意して使ってみてください。

参考

• http://bashalog.c-brains.jp/14/02/28-150000.php