【MySQL】TIMEDIFFの出力結果を「日・時・分・秒」形式に変換する方法

TIMEDIFFの結果を「日・時・分・秒」形式に変換するには

MySQLでTIMEDIFF関数の戻り値を「〇日〇時間〇分〇秒」という読みやすい形式に変換するには、CONCAT()関数を使います。あわせてFLOOR()やMOD()などの関数を組み合わせることで、24時間を超える差分も「日」と「時間」にきれいに分割できます。

ここからは、実際にサンプルテーブルを作成し、手順を追って動作を確認していきます。

1. サンプルテーブルを作成する

まず、開始日時と終了日時を格納するためのテーブルを作成します。テーブル作成のクエリは以下のとおりです。

mysql> create table convertTimeDifferenceDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> StartDate datetime,
    -> EndDate datetime,
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.68 sec)

2. テストデータを挿入する

続いて、INSERTコマンドで複数のレコードを追加します。date_add()を使うと、現在時刻から相対的な日時を簡単に生成できるので便利です。

mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values(date_add(now(),interval -3 hour),date_add(now(),interval 3 hour));
Query OK, 1 row affected (0.41 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values(date_add(now(),interval -2 hour),date_add(now(),interval 2 hour));
Query OK, 1 row affected (0.27 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values('2018-04-05 12:30:35','2018-05-17 14:30:50');
Query OK, 1 row affected (0.14 sec)
mysql> insert into convertTimeDifferenceDemo(StartDate,EndDate) values('2017-10-11 11:20:30','2017-12-17 15:21:55');
Query OK, 1 row affected (0.20 sec)

3. 登録データを確認する

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

mysql> select *from convertTimeDifferenceDemo;

実行結果は次のとおりです。

+----+---------------------+---------------------+
| Id | StartDate           | EndDate             |
+----+---------------------+---------------------+
|  1 | 2019-01-28 20:55:33 | 2019-01-29 02:55:33 |
|  2 | 2019-01-28 21:57:42 | 2019-01-29 01:57:42 |
|  3 | 2018-04-05 12:30:35 | 2018-05-17 14:30:50 |
|  4 | 2017-10-11 11:20:30 | 2017-12-17 15:21:55 |
+----+---------------------+---------------------+
4 rows in set (0.00 sec)

4. 差分を「日・時・分・秒」形式に変換するクエリ

TIMEDIFF()で取得した差分をHOUR()・MINUTE()・SECOND()でそれぞれ取り出し、FLOOR()とMOD()を使って24時間単位の値を「日」と「時間」に振り分けます。最後にCONCAT()で文字列として連結すれば、人が読みやすい形で出力できます。

mysql> SELECT CONCAT(
    -> FLOOR(HOUR(TIMEDIFF(StartDate,EndDate)) / 24), ' 日 ',
    -> MOD(HOUR(TIMEDIFF(StartDate,EndDate)), 24), ' 時間 ',
    -> MINUTE(TIMEDIFF(StartDate,EndDate)), ' 分 ',
    -> SECOND(TIMEDIFF(StartDate,EndDate)), ' 秒') AS 経過時間
    -> FROM convertTimeDifferenceDemo;

実行結果は以下のようになります。

+--------------------------------+
| 経過時間                       |
+--------------------------------+
| 0 日 6 時間 0 分 0 秒          |
| 0 日 4 時間 0 分 0 秒          |
| 34 日 22 時間 59 分 59 秒      |
| 34 日 22 時間 59 分 59 秒      |
+--------------------------------+
4 rows in set, 6 warnings (0.04 sec)

注意点：TIME型の範囲制限について

TIMEDIFF()が返すTIME型には「-838:59:59」から「838:59:59」までという表現範囲の制限があります。これは「34日22時間59分59秒」に相当するため、差分がこれを超える3行目・4行目のレコードは上限で丸められ、どちらも同じ結果になっています。実行時に「6 warnings」と表示されるのも、この桁あふれによるものです。

より長い期間を正確に計算したい場合は、TIMESTAMPDIFF(unit, 開始日時, 終了日時)を使って日数や秒数を直接求める方法も検討するとよいでしょう。

まとめ

MySQLのTIMEDIFF()の結果を「日・時・分・秒」の形式に整えるには、CONCAT()とFLOOR()、MOD()を組み合わせるのが基本のテクニックです。ただしTIME型には約35日弱という上限があるため、長期間の差分を扱うケースではTIMESTAMPDIFF()の活用をおすすめします。