【MySQL】日付時刻関数を使用して、日付や時刻の差分を取得する

DB&サーバ

2014.02.28

こんにちはfukasawaです。ダイエットのためにアンクルウェイトを購入したのですが、足首が太すぎて巻けませんでした。痩せねば。

さて、今回はMySQLの日付時刻関数を使用して、日付、時刻の差分を取得する方法についてです。PERIOD_DIFF(),DATEDIFF(),TIMEDIFF(),TIMESTAMPDIFF()を使用して日付や時刻の差分を求める方法について調べてみました。

※MySQL 5.6.14で検証しています

DATEDIFF(expr,expr2)

開始日 expr と終了日 expr2 までの日数を取得します。(v4.1.1以降)


SELECT DATEDIFF('2013-12-31','2013-02-20');
	-> 314
SELECT DATEDIFF('2013-12-31','2014-02-20');
	-> -51
SELECT DATEDIFF('2013-12-31', CURRENT_DATE());
	-> -58

expr,expr2には日付式(YYYY-MM-DD)もしくは日付時刻式(YYYY-MM-DD HH:MM:SS[.fraction])を指定します。
日付時刻式を指定しても、時刻部分は無視されます。


SELECT DATEDIFF('2013-12-31 12:12:00','2013-02-20 13:12:11');
	-> -51

不正な引数を渡した場合はNULLが返ってくるようです。


SELECT DATEDIFF('2013-12-31','2013-13-20');
	-> NULL

例） accountテーブルのパスワード有効期限（expires DATE型）を参照し、期限までの日数が7日未満のレコードを取得する。


SELECT * FROM account WHERE DATEDIFF(expires, CURRENT_DATE() ) < 7;

TIMEDIFF(expr,expr2)

開始時刻 expr と終了時刻 expr2 までの時刻の差を取得します。(v4.1.1以降)


SELECT TIMEDIFF('2014:02:01 23:00:00','2014:01:30 00:00:00');
	-> 71:00:00
SELECT TIMEDIFF('2013:12:31 23:00:00','2014:01:01 00:00:00.000000');
	-> -01:00:00.000000
SELECT TIMEDIFF('2014-02-27 00:00:00',CURRENT_TIMESTAMP());
	-> -19:24:01
SELECT TIMEDIFF('11:30:00','00:00:00');
	-> 11:30:00

expr,expr2には時刻式('HH:MM:SS')もしくは日付時刻式('YYYY-MM-DD HH:MM:SS[.fraction]')を指定します。引数の型はそろえる必要があります。

DATEDIFF()と同様、不正な引数を渡した場合はNULLが返ってくるようです。


SELECT TIMEDIFF('2014:01:02 00:00:00','2014:13:02 00:00:00');
	-> NULL

PERIOD_DIFF(P1,P2)

期間 P1,P2までの月数の差を取得します。(v4.1.1以降)

DATEDIFF(),TIMEDIFF()と違って関数名にアンダーバーが入っています。


SELECT PERIOD_DIFF('201402','201312');
	-> 2
SELECT PERIOD_DIFF('201402','201412');
	-> -10
SELECT PERIOD_DIFF('9802','9512');
	-> 26

P1,P2にはYYMM または YYYYMMを指定します。日付は指定できません。

DATEDIFF(),TIMEDIFF()とは違い、不正な引数を渡した場合はNULLにならず、謎の数値(?)が返ってくるようです。


SELECT PERIOD_DIFF('201312','20a314');
	-> 148

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

日付式または日付時刻式 datetime_expr1,datetime_expr2 間の差を整数で取得します。(v5.0.0以降)


SELECT TIMESTAMPDIFF(MONTH,'2013-02-01','2014-03-01');
	-> 13
SELECT TIMESTAMPDIFF(YEAR,'2013-02-01','2014-03-01');
	-> 1
SELECT TIMESTAMPDIFF(HOUR,'2014-02-01','2014-02-03');
	-> 48

上記の例では引数で渡した日付の差を、それぞれ月数、年数、時間で取得しています。
DATEDIFF()やTIMEDIFF()は「左の引数 - 右の引数」の結果を出力していたのに TIMESTAMPDIFF()では逆になっているようです。

第一引数のunitにはMONTH、YEAR、HOURの他にも、以下の値を指定することができます。