MySQL 日付と時刻の関数一覧

MySQL で日付、時刻を操作するための、主な SQL 関数です。

MySQL には SQL Server と同等以上の様々関数が用意されています。

SQL の関数を上手に活用して、データを利用する側のプログラムを、少しでも簡単に実装しましょう。

MySQL の日付・時刻を操作する主な関数

関数名	概要
ADDDATE	日時を加算した値を返す
ADDTIME	時刻を加算した値を返す
CURRENT_DATE CURDATE	現在の日付を返す
CURRENT_TIME CURTIME	現在の時刻を返す
CURRENT_TIMESTAMP	現在の日時を返す (ANSI SQL)
DATE	日時の日付部分を返す
DATE_ADD	日時を加算した値を返す
DATE_FORMAT	日付を指定した書式に変換した値 (文字列) を返す
DATE_SUB	日時を減算した値を返す
DATEDIFF	日時の差を取得する
DAY	日時の日部分を返す
DAYNAME	日時の曜日名を返す
DAYOFMONTH	月の日を返す
DAYOFWEEK	週の日を返す
DAYOFYEAR	年間の通算日を返す
EXTRACT	日時の指定した要素部分の値を返す
HOUR	日時の時部分を返す
LAST_DAY	月の最終日を返す
LOCALTIME	現在の日時を返す
LOCALTIMESTAMP	現在の日時を返す
MINUTE	日時の分部分を返す
MONTH	日時の月部分を返す
MONTHNAME	日時の月名を返す
NOW	現在の日時を返す
QUARTER	日時の四半期部分を返す
SECOND	日時の秒部分を返す
SUBDATE	日時を減算した値を返す
SYSDATE	現在の日付を返す
TIME	日時の時間部分を返す
TIME_FORMAT	時刻を指定した書式に変換した値 (文字列) を返す
WEEK	年間の通算週を返す
WEEKDAY	日時の曜日部分を返す
WEEKOFYEAR	年間の通算週を返す
YEAR	日時の年部分を返す

1 日付と時刻を取得する関数
2 日付と時刻の要素 (日時の一部分) を抜き出した値を取得する関数
3 日付と時刻の特定の値を取得する関数
4 日付と時刻を計算する関数
5 日時を整形した値を取得する関数
- 5.1 指定した書式 DATE_FORMAT
- 5.2 TIME_FORMAT

日付と時刻を取得する関数

日付を取得する CURRENT_DATE・CURDATE

現在の日付を取得したい時に使います。

CURRENT_DATE は () があってもなくても OK です。

/* 日付を取得する */
SELECT
	CURRENT_DATE,
	CURRENT_DATE(),
	CURDATE();

/* 日付を取得する */

SELECT

CURRENT_DATE,

CURRENT_DATE(),

CURDATE();

実行結果

関数名	値
CURRENT_DATE	2015-03-08
CURRENT_DATE()	2015-03-08
CURDATE	2015-03-08

時刻を取得する CURRENT_TIME・CURTIME

現在の時刻を取得したい時に使います。

CURRENT_TIME は () があってもなくても OK です。

/* 時刻を取得する */
SELECT
	CURRENT_TIME,
	CURRENT_TIME(),
	CURTIME();

/* 時刻を取得する */

SELECT

CURRENT_TIME,

CURRENT_TIME(),

CURTIME();

実行結果

関数名	値
CURRENT_TIME	16:57:07
CURRENT_TIME()	16:57:07
CURTIME	16:57:07

日時を取得する CURRENT_TIMESTAMP・NOW・LOCALTIME・LOCALTIMESTAMP・SYSDATE

現在の日時を取得したい時に使います。

CURRENT_TIMESTAMP, LOCALTIME は () があってもなくても OK です。

/* 日時を取得する */
SELECT
	CURRENT_TIMESTAMP,
	CURRENT_TIMESTAMP(),
	NOW(),
	LOCALTIME,
	LOCALTIME(),
	SYSDATE();

/* 日時を取得する */

SELECT

CURRENT_TIMESTAMP,

CURRENT_TIMESTAMP(),

NOW(),

LOCALTIME,

LOCALTIME(),

SYSDATE();

実行結果

関数名	値
CURRENT_TIMESTAMP	2015-03-08 16:57:07
CURRENT_TIMESTAMP()	2015-03-08 16:57:07
NOW()	2015-03-08 16:57:07
LOCALTIME	2015-03-08 16:57:07
LOCALTIME()	2015-03-08 16:57:07
SYSDATE	2015-03-08 16:57:07

「N」「O」「W」「(」「)」の５文字で済みますので、NOW が一番使いやすいです。

日付と時刻の要素 (日時の一部分) を抜き出した値を取得する関数

年を取得する YEAR

日時の年部分を取得したい時に使います。

SELECT YEAR('2001-02-03');

1	SELECT YEAR('2001-02-03');

実行結果

2001

月を取得する MONTH

日時の月部分を取得したい時に使います。

SELECT MONTH('2001-02-03');

1	SELECT MONTH('2001-02-03');

実行結果

日を取得する DAY

日時の日部分を取得したい時に使います。

SELECT DAY('2001-02-03');

1	SELECT DAY('2001-02-03');

実行結果

日付を取得する DATE

日時の日付部分を取得したい時に使います。

SELECT DATE('2001-02-03 04:05:06');

1	SELECT DATE('2001-02-03 04:05:06');

実行結果

2001-02-03

曜日を取得する WEEKDAY

日時の曜日部分のインデックスを取得したい時に使います。
取得できる値は「0:月曜日 Monday ～ 6:日曜日 Sunday」となります。

SELECT WEEKDAY('2001-02-03 04:05:06');

1	SELECT WEEKDAY('2001-02-03 04:05:06');

実行結果

時を取得する HOUR

日時の時部分を取得したい時に使います。

SELECT HOUR('2001-02-03 04:05:06');

1	SELECT HOUR('2001-02-03 04:05:06');

実行結果

分を取得する MINUTE

日時の分部分を取得したい時に使います。

SELECT MINUTE('2001-02-03 04:05:06');

1	SELECT MINUTE('2001-02-03 04:05:06');

実行結果

秒を取得する SECOND

日時の秒部分を取得したい時に使います。

SELECT SECOND('2001-02-03 04:05:06');

1	SELECT SECOND('2001-02-03 04:05:06');

実行結果

時刻を取得する TIME

日時の時刻部分を取得したい時に使います。

SELECT TIME('2001-02-03 04:05:06');

1	SELECT TIME('2001-02-03 04:05:06');

実行結果

04:05:06

実行結果

モード	値
0	4
1	5
2	4
3	5
4	5
5	5
6	5
7	5
なし	4

年間の通算週を取得する WEEKOFYEAR

WEEK 関数と同様に、年間の通算週を取得したい時に使います。

WEEKOFYEAR 関数は 1 ～ 53 までの範囲内で、通算週を返します。WEEK 関数のモードに 3 を指定した場合と同様の結果が得られます。

SELECT WEEKOFYEAR('2001-02-03 04:05:06');

1	SELECT WEEKOFYEAR('2001-02-03 04:05:06');

実行結果

日付と時刻の特定の値を取得する関数

1月1日からの通算日を取得する DAYOFYEAR

1月1日を1として通算した日を取得したい時に使います。
1月31日なら31、2月1日なら32が取得できます。

SELECT DAYOFYEAR('2001-02-03 04:05:06');

1	SELECT DAYOFYEAR('2001-02-03 04:05:06');

実行結果

月の日を取得する DAYOFMONTH

毎月1日を1として通算した日を取得したい時に使います。
月ごとの日を返すので、日時の日部分と同じ値、つまり DAY 関数と同じ値が取得できます。

SELECT DAYOFMONTH('2001-02-03 04:05:06');

1	SELECT DAYOFMONTH('2001-02-03 04:05:06');

実行結果

週の日を取得する DAYOFWEEK

毎週1日を1として通算した日を取得したい時に使います。
取得できる値は「1:日曜日 Sunday ～ 7:土曜日 Saturday」となります。
WEEKDAY 関数が月曜日からスタートなのに対して、DAYOFWEEK 関数は日曜日からスタートとなっています。

SELECT DAYOFWEEK('2001-02-03 04:05:06');

1	SELECT DAYOFWEEK('2001-02-03 04:05:06');

実行結果

年間の通算週を取得する WEEK

年間の通算週を取得したい時に使います。
WEEK 関数には第２引数にモードをとる形式が用意されており、モードには 0 ～ 7 までの値を設定することができます。
モードの値によって、WEEK 関数の戻り値は次のように変化します。
第２引数を省略した場合は、引数に 0 を指定した結果と同じ値を返します。

モード	週の開始曜日	第 1 週の判定条件	戻り値の範囲
0	日曜日	パターン１	0～53
1	月曜日	パターン２	0～53
2	日曜日	パターン１	1～53
3	月曜日	パターン２	1～53
4	日曜日	パターン２	0～53
5	月曜日	パターン３	0～53
6	日曜日	パターン２	1～53
7	月曜日	パターン３	1～53

パターン１：日曜日を含む
パターン２：7 日のうち 4 日以上を含む
パターン３：月曜日を含む

モードでは、週が日曜日と月曜日のどちらから始まるのかと、戻り値が 0 ～ 53までと 1 ～ 53 までのいづれの範囲であるのかを指定できます。

SELECT
	WEEK('2001-02-03 04:05:06', 0),
	WEEK('2001-02-03 04:05:06', 1),
	WEEK('2001-02-03 04:05:06', 2),
	WEEK('2001-02-03 04:05:06', 3),
	WEEK('2001-02-03 04:05:06', 4),
	WEEK('2001-02-03 04:05:06', 5),
	WEEK('2001-02-03 04:05:06', 6),
	WEEK('2001-02-03 04:05:06', 7),
	WEEK('2001-02-03 04:05:06');

SELECT

WEEK('2001-02-03 04:05:06', 0),

WEEK('2001-02-03 04:05:06', 1),

WEEK('2001-02-03 04:05:06', 2),

WEEK('2001-02-03 04:05:06', 3),

WEEK('2001-02-03 04:05:06', 4),

WEEK('2001-02-03 04:05:06', 5),

WEEK('2001-02-03 04:05:06', 6),

WEEK('2001-02-03 04:05:06', 7),

WEEK('2001-02-03 04:05:06');

月の名前を取得する MONTHNAME

日付の月の名前を取得したい時に使います。

SELECT MONTHNAME('2001-02-03');

1	SELECT MONTHNAME('2001-02-03');

実行結果

February

曜日の名前を取得する DAYNAME

日付の曜日の名前を取得したい時に使います。

SELECT DAYNAME('2001-02-03');

1	SELECT DAYNAME('2001-02-03');

実行結果

Saturday

日付の属する月の末日を取得する LAST_DAY

日付を含む月の最終日を取得したい時に使います。

SELECT LAST_DAY('2001-02-03');

1	SELECT LAST_DAY('2001-02-03');

実行結果

2001-02-28

日付の属する月の末日を取得する QUARTER

日付に対応する四半期をを取得したい時に使います。
実行結果は、四半期を表す 1 ～ 4 の数値を返します。

SELECT QUARTER('2001-02-03');

1	SELECT QUARTER('2001-02-03');

実行結果

日付と時刻を計算する関数

日時を加算した値を取得する DATE_ADD, ADDDATE

日時に日時を加えた結果を取得したい時に使います。

DATE_ADD

DATE_ADD 関数には第１引数に日時式、第２引数に INTERVAL 加算する日時、第３引数に書式を指定します。

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL 1 DAY);
SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL 1 SECOND);
SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '1:1' MINUTE_SECOND);
SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);
SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '-1 10' DAY_HOUR);

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL 1 DAY);

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL 1 SECOND);

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '1:1' MINUTE_SECOND);

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '1 1:1:1' DAY_SECOND);

SELECT DATE_ADD('2001-02-03 00:00:00', INTERVAL '-1 10' DAY_HOUR);

指定するユニット値と書式の一覧

ユニット値	書式
MICROSECOND	マイクロ秒数
SECOND	秒数
MINUTE	分数
HOUR	時数
DAY	日数
WEEK	週数
MONTH	月数
QUARTER	四半期数
YEAR	年数
SECOND_MICROSECOND	‘秒数.マイクロ秒数’
MINUTE_MICROSECOND	‘分数:秒数.マイクロ秒数’
MINUTE_SECOND	‘分数:秒数’
HOUR_MICROSECOND	‘時数:分数:秒数.マイクロ秒数’
HOUR_SECOND	‘時数:分数:秒数’
HOUR_MINUTE	‘時数:分数’
DAY_MICROSECOND	‘日数時数:分数:秒数.マイクロ秒数’
DAY_SECOND	‘日数時数:分数:秒数’
DAY_MINUTE	‘日数時数:分数’
DAY_HOUR	‘日数時数’
YEAR_MONTH	‘年数-月数’

ADDDATE

ADDDATE 関数も DATE_ADD と同様の構文で使用することができます。
また、第２引数に整数を指定すると、加算する日数として処理されます。

SELECT ADDDATE('2001-02-03', 1);

1	SELECT ADDDATE('2001-02-03', 1);

実行結果

2001-02-04

日時を減算した値を取得する DATE_SUB, SUBDATE

日時から日時を差し引いた結果を取得したい時に使います。

DATE_SUB

DATE_SUB 関数は DATE_ADD 関数と同様に、第１引数に日時式、第２引数に INTERVAL 加算する日時、第３引数に書式を指定します。

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL 1 DAY);
SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL 1 SECOND);
SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '1 1:1:1' DAY_SECOND);
SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '-1 10' DAY_HOUR);

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL 1 DAY);

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL 1 SECOND);

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '1:1' MINUTE_SECOND);

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '1 1:1:1' DAY_SECOND);

SELECT DATE_SUB('2001-02-03 23:59:59', INTERVAL '-1 10' DAY_HOUR);

実行結果

2001-02-02 23:59:59
2001-02-03 23:59:58
2001-02-03 23:58:58
2001-02-02 22:58:58
2001-02-05 09:59:59

SUBDATE

SUBDATE 関数も DATE_SUB と同様の構文で使用することができます。
また、ADDDATE 関数と同様に、第２引数に整数を指定すると、減算する日数として処理されます。

SELECT SUBDATE('2001-02-03', 1);

1	SELECT SUBDATE('2001-02-03', 1);

実行結果

2001-02-02

時間を加算した値を取得する ADDTIME

時間を加算した結果を取得したい時に使います。

第１引数に日時式を指定し、第２引数に加算する時間式を指定すると、時間を加算した日時を返します。

SELECT ADDTIME('2001-12-31 23:59:59.999999', '1 1:1:1.000002');

1	SELECT ADDTIME('2001-12-31 23:59:59.999999', '1 1:1:1.000002');

実行結果

2002-01-02 01:01:01.000001

第１引数に時刻式を指定し、第２引数に加算する時間式を指定すると、時間を加算した時刻を返します。

SELECT ADDTIME('01:02:03.999999', '02:03:04.999998');

1	SELECT ADDTIME('01:02:03.999999', '02:03:04.999998');

実行結果

03:05:08.999997

日時の差を取得する DATEDIFF

2 つの日時の差を取得したい時に使います。

引数１より引数２が後の日付の場合

SELECT DATEDIFF('2001-2-3','2001-2-4');

1	SELECT DATEDIFF('2001-2-3','2001-2-4');

実行結果

-1

引数１より引数２が前の日付の場合

SELECT DATEDIFF('2001-2-3','2001-2-2');

1	SELECT DATEDIFF('2001-2-3','2001-2-2');

実行結果

日時を整形した値を取得する関数

指定した書式 DATE_FORMAT

日時を指定した書式に従って、変換した値を取得したい時に使います。

SELECT DATE_FORMAT('2001-02-03 04:05:06', '%W %M %Y');
SELECT DATE_FORMAT('2001-02-03 04:05:06', '%H:%i:%s');

1 2	SELECT DATE_FORMAT('2001-02-03 04:05:06', '%W %M %Y'); SELECT DATE_FORMAT('2001-02-03 04:05:06', '%H:%i:%s');

実行結果

Saturday February 2001
04:05:06

書式指定子一覧

指定子	説明
%a	簡略曜日名 (Sun..Sat)
%b	簡略月名 (Jan..Dec)
%c	月、数字 (0..12)
%D	英語のサフィクスを持つ日付 (0th, 1st, 2nd, 3rd, …)
%d	日、数字 (00..31)
%e	日、数字 (0..31)
%f	マイクロ秒 (000000..999999)
%H	時間 (00..23)
%h	時間 (01..12)
%I	時間 (01..12)
%i	分、数字 (00..59)
%j	年間通算日 (001..366)
%k	時 (0..23)
%l	時 (1..12)
%M	月名 (January..December)
%m	月、数字 (00..12)
%p	AM または PM
%r	時間、12 時間単位 (hh:mm:ss に AM または PM が続く)
%S	秒 (00..59)
%s	秒 (00..59)
%T	時間、24 時間単位 (hh:mm:ss)
%U	週 (00..53)、日曜日が週の初日、WEEK() モード 0
%u	週 (00..53)、月曜日が週の初日、WEEK() モード 1
%V	週 (01..53)、日曜日が週の初日、WEEK() モード 2、%X とともに使用
%v	週 (01..53)、月曜日が週の初日、WEEK() モード 3、%x とともに使用
%W	曜日名 (Sunday..Saturday)
%w	曜日 (0=Sunday..6=Saturday)
%X	年間の週、日曜日が週の初日、数字、4 桁、%V とともに使用
%x	年間の週、月曜日が週の初日、数字、4 桁、%v とともに使用
%Y	年、数字、4 桁
%y	年、数字 (2 桁)
%%	リテラル「%」文字
%x	x (上記にないすべての「x」)

TIME_FORMAT

時刻を指定した書式に従って、変換した値を取得したい時に使います。

SELECT TIME_FORMAT('23:59:59', '%H %k %h %I %l %i %s');

1	SELECT TIME_FORMAT('23:59:59', '%H %k %h %I %l %i %s');

実行結果

23 23 11 11 11 59 59

参考URL
日付および時間関数