at backyard

Color my life with the chaos of trouble.

MySQLで時刻や日付の差分を取得する方法 (TIMEDIFFやDATEDIFFの使い方)

MySQL DATEDIFF TIMEDIFF 備忘録

MySQLでTIMEDIFFやDATEDIFFを用いて、時刻や日付の差分を取得する

MySQLで時刻や日付の差分を取得するには TIMEDIFF や DATEDIFF が使える。

基本的な使い方を下記にメモした。
一応現在接続している自身のMySQLのversionはこちら。

select version();
# ⇒ 5.7.21

なお、MySQLは、dockerで立てたMySQL にローカルPCから接続している(余談)。
そういえば昔、そんな備忘録もちょこっと書いたなと思ったので、自分自身が思い出すために貼っておく。

mac上のDockerに立ち上げたmysqlに、host(mac)から接続する - at backyard

TIMEDIFFを使ってMySQLで時刻の差分を取得する

第１引数 - 第２引数という形で計算されるので注意
使い方は下記の通り。

SELECT TIMEDIFF('2000:01:01 00:10:00','2000:01:01 00:00:00');
# ⇒ 00:10:00

SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 00:10:00');
# ⇒ -00:10:00

SELECT TIMEDIFF('2000/01/02 00:00:00','2000/01/01 23:59:59');
# ⇒ 00:00:01

あくまで時刻の差分となるため、１日以上離れている場合でも時間で算出される。

SELECT TIMEDIFF('2000/02/01 00:00:00','2000/01/01 23:59:59');
# ⇒ 720:00:01

時刻の記述は異なっていても、それが正しい値であればちゃんと計測されるよう。

SELECT TIMEDIFF('2000:01:01 00:10:00','2000/01/01 00/00/00');
# ⇒ 00:10:00

日付と時刻の比較はできないようで NULL が返る

SELECT TIMEDIFF('2000:01:01 00:01:00','2000:01:01');
# ⇒ NULL

また、正しくない時刻の場合も NULL が返る。

SELECT TIMEDIFF('2000:01:01 00:00:00','2000:01:01 24:60:00');
# ⇒ NULL

SELECT TIMEDIFF('2000:01:01 00:00:00','2000:13:01 00:00:00');
# ⇒ NULL

DATEDIFFを使ってMySQLで日付の差分を取得する

DATEDIFFは日付の差分を算出する。
こちらも第１引数 - 第２引数という形で計算される。

SELECT DATEDIFF('2000:01:02','2000:01:01');
# ⇒ 1

SELECT DATEDIFF('2000/01/02','2000/01/01');
# ⇒ 1

SELECT DATEDIFF('2000/01/02','2001/01/01');
# ⇒ 365

SELECT DATEDIFF('2000:01:02','2020:1:1');
# ⇒ -7304

DATEDIFFはあくまで日付の比較となるため、仮に時刻を含む値で比較した場合でも日付自体に差異が出ていれば計算される。

SELECT DATEDIFF('2000:01:02 00:00:00','2000:01:01 23:59:59');
# ⇒ 1

SELECT DATEDIFF('2000:01:01 00:00:00','2000:01:01 23:59:59');
# ⇒ 0

また、日付と時刻の比較も可能。
(時刻自体は無視されて比較されているよう)

SELECT DATEDIFF('2000/01/02','2000/01/01 23:59:59');
# ⇒ 1

SELECT DATEDIFF('2000/01/01','2000/01/01 23:59:59');
# ⇒ 0

こちらも正しくない値を渡すと NULL が返る

SELECT DATEDIFF('2000:01:02','2000:02:31');
# ⇒ NULL

SELECT DATEDIFF('2000:01:02','23:59:59');
# ⇒ NULL

MySQLのTIMEDIFFを用いて、開始から終了までの時間が短い順にレコードを表示していく

これらの関数を用いれば、例えば開始時刻から終了時刻までの経過時間が短い順にレコードを表示するようなことができる。
(ここでは時刻の差分を取得するためにTIEMDIFFを使用)

SELECT
  id,
  TIMEDIFF(endDateTime, startDateTime) as "開始から終了までにかかった時間”
FROM
  table_name
ORDER BY TIMEDIFF(endDateTime, startDateTime) ASC;