MySQLで開始～終了日時の間隔を計算し分に変換して出力する方法

MySQLで「開始日時」「終了日時」のdatetime型カラムがあり、開始から終了までの間隔(差分)を計算した上で「合計〇分」のように分に変換して出力させたい。少々ややこしそうなのでPHP側での対応になるかと思ったが、MySQL側だけでなんとかなったので以下に対応方法をメモ。

テーブル構造

mysql> SHOW COLUMNS FROM `test_table`;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| start | datetime | NO   |     | NULL    |                |
| end   | datetime | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

データ内容

mysql> TABLE `test_table`;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| start | datetime | NO   |     | NULL    |                |
| end   | datetime | NO   |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+

対応方法

SQL文

SELECT 
    `start`, 
    `end`, 
    TRUNCATE(
        TIME_TO_SEC(
            TIMEDIFF(`end`, `start`)
        ) / 60, 0
    ) AS `diff_minute` 
FROM test_table;

出力例

+---------------------+---------------------+-------------+
| start               | end                 | diff_minute |
+---------------------+---------------------+-------------+
| 2025-01-01 00:00:00 | 2025-01-01 01:00:00 |          60 |
| 2025-01-01 00:00:00 | 2025-01-01 05:00:00 |         300 |
| 2025-01-01 00:00:00 | 2025-01-01 02:30:00 |         150 |
+---------------------+---------------------+-------------+

解説等

TIMEDIFFで時間の差分を求め、その差分をTIME_TO_SECで秒に変換後、60で割って分に変換し、TRUNCATEで小数点以下を切り捨てている。

今回は開始日時、終了日時共に秒までは指定しない形だったが、指定する場合はTRUNCATE辺りを調整する必要がありそう。

リファレンス

TIMEDIFF

https://dev.mysql.com/doc/refman/8.0/ja/date-and-time-functions.html#function_timediff

TIME_TO_SEC

https://dev.mysql.com/doc/refman/8.0/ja/date-and-time-functions.html#function_time-to-sec

TRUNCATE

https://dev.mysql.com/doc/refman/8.0/ja/mathematical-functions.html#function_truncate