MySQLで2つのタイムスタンプの差を秒単位で取得する方法

MySQLでは、組み込み関数UNIX_TIMESTAMP()を使うことで、2つのタイムスタンプの差を秒単位で簡単に計算できます。UNIX_TIMESTAMP()は、指定した日時をUnixエポック（1970年1月1日 00:00:00 UTC）からの経過秒数に変換する関数です。この変換結果同士を引き算すれば、その差が秒数として得られます。

基本構文

SELECT UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2) AS anyVariableName FROM yourTableName;

サンプルテーブルの作成

それでは、実際にテーブルを作成して動きを確認してみましょう。以下がテーブル作成用のクエリです。

mysql> create table DifferenceInSeconds
   -> (
   -> FirstTimestamp TIMESTAMP,
   -> SecondTimestamp TIMESTAMP
   -> );
Query OK, 0 rows affected (0.93 sec)

次に、INSERTコマンドでレコードを挿入します。

mysql> insert into DifferenceInSeconds values('2012-12-12 13:16:55','2012-12-12 13:13:55');
Query OK, 1 row affected (0.31 sec)

mysql> insert into DifferenceInSeconds values('2014-10-11 12:15:50','2014-10-11 12:13:50');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DifferenceInSeconds values('2018-12-14 13:30:53','2018-12-14 13:27:53');
Query OK, 1 row affected (0.21 sec)

SELECT文ですべてのレコードを表示してみます。

mysql> select *from DifferenceInSeconds;

実行結果は以下の通りです。

+---------------------+---------------------+
| FirstTimestamp      | SecondTimestamp     |
+---------------------+---------------------+
| 2012-12-12 13:16:55 | 2012-12-12 13:13:55 |
| 2014-10-11 12:15:50 | 2014-10-11 12:13:50 |
| 2018-12-14 13:30:53 | 2018-12-14 13:27:53 |
+---------------------+---------------------+
3 rows in set (0.00 sec)

2つのタイムスタンプの差（秒）を求めるクエリ

ここからが本題です。以下のクエリを実行すると、2つのタイムスタンプの差を秒単位で取得できます。

mysql> SELECT UNIX_TIMESTAMP(FirstTimestamp) - UNIX_TIMESTAMP(SecondTimestamp) as Seconds from DifferenceInSeconds;

実行結果は以下の通りです。

+---------+
| Seconds |
+---------+
|     180 |
|     120 |
|     180 |
+---------+
3 rows in set (0.00 sec)

このように、それぞれの行でタイムスタンプ間の差が「180秒」「120秒」「180秒」として出力されました。

どちらのタイムスタンプが大きいか分からない場合はABS()を使う

ポイント：どちらのカラムの日時が新しいか分からない場合は、ABS()関数を使うことで、常に正の値として差分を取得できます。

構文は以下の通りです。

SELECT ABS(UNIX_TIMESTAMP(yourColumnName1) - UNIX_TIMESTAMP(yourColumnName2)) as Seconds from DifferenceInSeconds;

動作を確認するために、1つ目のタイムスタンプの方が古いレコードを追加してみましょう。

mysql> insert into DifferenceInSeconds values('2018-12-14 13:26:53','2018-12-14 13:31:53');
Query OK, 1 row affected (0.21 sec)

テーブルの全レコードを表示します。

mysql> select *from DifferenceInSeconds;

実行結果は以下の通りです。

+---------------------+---------------------+
| FirstTimestamp      | SecondTimestamp     |
+---------------------+---------------------+
| 2012-12-12 13:16:55 | 2012-12-12 13:13:55 |
| 2014-10-11 12:15:50 | 2014-10-11 12:13:50 |
| 2018-12-14 13:30:53 | 2018-12-14 13:27:53 |
| 2018-12-14 13:26:53 | 2018-12-14 13:31:53 |
+---------------------+---------------------+
4 rows in set (0.00 sec)

最後の行では、FirstTimestampの方がSecondTimestampより5分（300秒）古くなっています。ここでABS()関数を使用したクエリを実行します。

mysql> SELECT ABS(UNIX_TIMESTAMP(FirstTimestamp) - UNIX_TIMESTAMP(SecondTimestamp)) as Seconds from DifferenceInSeconds;

実行結果は以下の通りです。

+---------+
| Seconds |
+---------+
|     180 |
|     120 |
|     180 |
|     300 |
+---------+
4 rows in set (0.00 sec)

注意：ABS()を使用しない場合、最後の行の出力は「-300」という負の値になります。順序が保証されないデータを扱う際は、ABS()を併用するのが安全です。