MySQLで2つの日付間の年・月・日・時・分・秒の期間を計算する関数を作成する方法

MySQLでは、2つの日付間の経過時間を「○年○ヶ月○日○時間○分○秒」という読みやすい形式で求めるユーザー定義関数を作成できます。ここでは、TIMESTAMPDIFF や CONCAT などの組み込み関数を活用し、年・月・日・時・分・秒ごとの差分を算出する Duration 関数の実装方法を解説します。

Duration関数の作成

まず、同名の関数が既に存在する場合は削除しておき、DELIMITER を変更したうえで関数を定義します。この関数は2つの DATETIME 型の値を受け取り、整形された期間文字列を CHAR(128) 型で返します。内部では TIMESTAMPDIFF で年・月・日の差分を求め、残りの時間部分を秒単位に変換してから時・分・秒へ分解しています。

mysql> DROP FUNCTION IF EXISTS Duration;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS Label123;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql> CREATE FUNCTION Duration( dtd1 datetime, dtd2 datetime ) RETURNS CHAR(128)
    -> BEGIN
    -> DECLARE yyr,mon,mmth,dy,ddy,hhr,m1,ssc,t1 BIGINT;
    -> DECLARE dtmp DATETIME;
    -> DECLARE t0 TIMESTAMP;
    -> SET yyr = TIMESTAMPDIFF(YEAR,dtd1,dtd2);
    -> SET mon = TIMESTAMPDIFF(MONTH,dtd1,dtd2);
    -> SET mmth = mon MOD 12;
    -> SET dtmp = ADDDATE(dtd1, interval mon MONTH);
    -> SET dy = TIMESTAMPDIFF(DAY,dtd1,dtd2);
    -> SET ddy = TIMESTAMPDIFF(DAY,dtmp,dtd2);
    -> SET t0 = TIMESTAMPADD(DAY,dy,dtd1);
    -> SET t1 = TIME_TO_SEC(TIMEDIFF(dtd2,t0));
    -> SET hhr = FLOOR(t1/3600);
    -> SET m1 = FLOOR(t1/60) - 60*hhr;
    -> SET ssc = t1 - 3600*hhr - 60*m1;
    -> RETURN CONCAT( Label123(yyr,'year'), Label123(mmth,'month'),
    ->     label123(ddy,'day'), Label123(hhr,'hour'),
    ->     Label123(m1,'min'), Label123(ssc,'sec')
    -> );
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

補助関数Label123の作成

Label123 は補助的な関数で、数値とラベル（year、month など）を受け取り、「10 years 」のような文字列を生成します。値が1の場合は単数形、それ以外の場合は複数形（末尾に "s" を追加）が自動的に適用されるため、出力が自然な英語表現になります。

mysql> CREATE FUNCTION Label123( ival int, clabel char(16) ) RETURNS VARCHAR(24)
    -> RETURN Concat( ival, ' ', clabel, If(ival=1,' ','s ') ); //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

実際に実行してみる

作成した関数に開始日時と終了日時を渡すと、以下のように整形された期間が出力されます。

mysql> Select Duration('2000-08-04 06:09:46', '2011-07-01 05:05:36')AS 'Duration';
+-----------------------------------------------------+
| Duration                                            |
+-----------------------------------------------------+
| 10 years 10 months 26 days 22 hours 55 mins 50 secs |
+-----------------------------------------------------+
1 row in set (0.00 sec)

このように、TIMESTAMPDIFF で大きな単位（年・月・日）の差分を順に求め、端数となる時間部分を秒に変換して時・分・秒へ分解することで、人間が直感的に理解できる形式の期間文字列を生成できます。レポート作成やログ分析など、経過期間を見やすく表示したい場面で非常に役立つテクニックです。