MySQLで異なる列間の時間差を計算する方法

MySQLでは、TIMEDIFF()関数とTIME_FORMAT()関数を組み合わせることで、異なる列に格納された時刻の差を簡単に計算できます。ここでは、出勤時間・退勤時間・休憩時間の3つの列から実勤務時間を算出する例を紹介します。

テーブルの作成

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> PunchIn time,
    -> PunchOut time,
    -> Launch time
    -> );
Query OK, 0 rows affected (0.50 sec)

データの挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。

mysql> insert into DemoTable values('9:00','6:00','1:00:00');
Query OK, 1 row affected (0.19 sec)

mysql> insert into DemoTable values('9:30','6:10','00:30:00');
Query OK, 1 row affected (0.21 sec)

レコードの確認

SELECT文を実行して、テーブル内のすべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

出力結果

+----------+----------+----------+
| PunchIn  | PunchOut | Launch   |
+----------+----------+----------+
| 09:00:00 | 06:00:00 | 01:00:00 |
| 09:30:00 | 06:10:00 | 00:30:00 |
+----------+----------+----------+
2 rows in set (0.00 sec)

時間差を計算するクエリ

以下のクエリでは、まずTIMEDIFF()でPunchInとPunchOutの差を求め、さらにその結果から休憩時間（Launch）を差し引いています。最後にTIME_FORMAT()を使って「時:分」形式に整形しています。

mysql> SELECT TIME_FORMAT(TIMEDIFF(TIMEDIFF(PunchIn,PunchOut), Launch), '%H:%i') as TimeDiff from DemoTable;

出力結果

+----------+
| TimeDiff |
+----------+
| 02:00    |
| 02:50    |
+----------+
2 rows in set (0.00 sec)

この結果について見てみると、1行目は「9:00 − 6:00 ＝ 3時間」から休憩1時間を引いた「02:00」、2行目は「9:30 − 6:10 ＝ 3時間20分」から休憩30分を引いた「02:50」となっています。このようにTIMEDIFF()を入れ子にすることで、複数の列にまたがる時間計算も柔軟に処理できます。