トラブルシューティング調査で、MySQLに構築したとあるテーブルのcreatedとmodifiedの差分計算をしたい時がよくある。他にもcreatedとNOW()の差分を出したい時なんかもあるのだけど、そんな時に便利な関数を見つけたので紹介。検証したMySQLバージョンは5.7.26。
TIMESTAMPDIFF()を検証するためのデータ準備
まずはデータ準備から。今回はusersテーブルを作っておく：
|
1 2 3 4 5 6 7 |
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; |
続いて、分析したいデータを準備：
|
1 2 3 4 5 6 7 8 9 |
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()の結果検証
3つのSELECT文を準備しつつ、結果を見てみる：
1.日時差分を秒単位で算出
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SELECT * ,TIMESTAMPDIFF(SECOND, created, modified) sec_diff FROM users ; ########## # result ########## 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) |
2.日時差分を分単位で算出
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SELECT * ,TIMESTAMPDIFF(MINUTE, created, modified) min_diff FROM users ; ########## # result ########## 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) |
3.日時差分を時単位で算出
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SELECT * ,TIMESTAMPDIFF(HOUR, created, modified) hour_diff FROM users ; ########## # result ########## 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など。マイクロ秒計算もいけるらしい。ほうほう。
参考）