MySQLで利用できる日付関数について確認します。日付の「比較」「加算」「差分」「抽出」など利用例を交えて解説します。
目次
抽出
SECOND｜秒を抽出
mysql> SELECT SECOND('2018-01-15 11:30:20');
+-------------------------------+
| SECOND('2018-01-15 11:30:20') |
+-------------------------------+
| 20 |
+-------------------------------+MINUTE｜分を抽出
mysql> SELECT MINUTE('2018-01-15 11:30:20');
+-------------------------------+
| MINUTE('2018-01-15 11:30:20') |
+-------------------------------+
| 30 |
+-------------------------------+HOUR｜時間を抽出
mysql> SELECT HOUR('2018-01-15 11:30:20');
+-----------------------------+
| HOUR('2018-01-15 11:30:20') |
+-----------------------------+
| 11 |
+-----------------------------+TIME｜時分秒を抽出
mysql> SELECT TIME('2018-01-15 11:30:20');
+-----------------------------+
| TIME('2018-01-15 11:30:20') |
+-----------------------------+
| 11:30:20 |
+-----------------------------+DAY｜日を抽出
mysql> SELECT DAY('2018-01-15 11:30:20');
+----------------------------+
| DAY('2018-01-15 11:30:20') |
+----------------------------+
| 15 |
+----------------------------+MONTH｜月を抽出
mysql> SELECT MONTH('2018-01-15 11:30:20');
+------------------------------+
| MONTH('2018-01-15 11:30:20') |
+------------------------------+
| 1 |
+------------------------------+YEAR｜年を抽出
mysql> SELECT YEAR('2018-01-15 11:30:20');
+-----------------------------+
| YEAR('2018-01-15 11:30:20') |
+-----------------------------+
| 2018 |
+-----------------------------+DATE｜日付を抽出
mysql> SELECT DATE('2018-01-15 11:30:20');
+-----------------------------+
| DATE('2018-01-15 11:30:20') |
+-----------------------------+
| 2018-01-15 |
+-----------------------------+WEEKDAY｜曜日番号を抽出
( 0 = Monday 1 = Tuesday … 6 = Sunday )
mysql> SELECT WEEKDAY('2018-01-15 11:30:20');
+--------------------------------+
| WEEKDAY('2018-01-15 11:30:20') |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)WEEK｜週番号を抽出
mysql> SELECT WEEK('2018-01-15 11:30:20');
+-----------------------------+
| WEEK('2018-01-15 11:30:20') |
+-----------------------------+
| 2 |
+-----------------------------+LAST_DAY｜月末を抽出
mysql> SELECT LAST_DAY('2018-01-22');
+------------------------+
| LAST_DAY('2018-01-22') |
+------------------------+
| 2018-01-31 |
+------------------------+現在の日時
CURRENT_DATE｜現在の日付
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2018-08-31 |
+----------------+CURRENT_TIME｜現在の時間
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 07:06:46 |
+----------------+NOW｜現在の日時
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-08-31 07:06:46 |
+---------------------+CURRENT_TIMESTAMP｜NOWのシノニム
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2018-08-31 07:06:46 |
+---------------------+UTC_DATE｜現在の UTC 日付
mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2018-08-30 |
+------------+UTC_TIME｜現在の UTC 時間
mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 22:06:46 |
+------------+UTC_TIMESTAMP｜現在の UTC 日時
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2018-08-30 22:06:46 |
+---------------------+加算・減算
ADDTIME｜時間を加算
mysql> SELECT ADDTIME('2018-01-15 11:30:20', '01:10:10');
+--------------------------------------------+
| ADDTIME('2018-01-15 11:30:20', '01:10:10') |
+--------------------------------------------+
| 2018-01-15 12:40:30 |
+--------------------------------------------+SUBTIME｜時間を減算
mysql> SELECT SUBTIME('2018-01-15 11:30:20', '01:10:10');
+--------------------------------------------+
| SUBTIME('2018-01-15 11:30:20', '01:10:10') |
+--------------------------------------------+
| 2018-01-15 10:20:10 |
+--------------------------------------------+DATE_ADD｜時間値 (間隔) を加算
mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2018-01-15 11:30:21 |
+----------------------------------------------------+
mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-01-16 11:30:20 |
+-------------------------------------------------+
mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND);
+-----------------------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2018-01-15 11:31:21 |
+-----------------------------------------------------------------+
mysql> SELECT DATE_ADD('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND);
+-------------------------------------------------------------------+
| DATE_ADD('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND) |
+-------------------------------------------------------------------+
| 2018-01-16 12:31:21 |
+-------------------------------------------------------------------+DATE_SUB｜時間値 (間隔) を減算
mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2018-01-15 11:30:19 |
+----------------------------------------------------+
mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-01-14 11:30:20 |
+-------------------------------------------------+
mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND);
+-----------------------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL '01:01' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2018-01-15 11:29:19 |
+-----------------------------------------------------------------+
mysql> SELECT DATE_SUB('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND);
+-------------------------------------------------------------------+
| DATE_SUB('2018-01-15 11:30:20', INTERVAL '1 01:01:01' DAY_SECOND) |
+-------------------------------------------------------------------+
| 2018-01-14 10:29:19 |
+-------------------------------------------------------------------+間隔
DATEDIFF｜日付の差
mysql> SELECT DATEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20');
+--------------------------------------------------------+
| DATEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20') |
+--------------------------------------------------------+
| 5 |
+--------------------------------------------------------+TIMEDIFF｜時間の差
mysql> SELECT TIMEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20');
+--------------------------------------------------------+
| TIMEDIFF('2018-01-20 13:40:30', '2018-01-15 11:30:20') |
+--------------------------------------------------------+
| 122:10:10 |
+--------------------------------------------------------+TIMESTAMPDIFF｜指定単位の差
mysql> SELECT TIMESTAMPDIFF(MINUTE, '2018-01-15 11:30:20', '2018-01-15 13:40:30');
+---------------------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE, '2018-01-15 11:30:20', '2018-01-15 13:40:30') |
+---------------------------------------------------------------------+
| 130 |
+---------------------------------------------------------------------+
mysql> SELECT TIMESTAMPDIFF(DAY, '2018-01-15 11:30:20', '2018-01-20 13:40:30');
+------------------------------------------------------------------+
| TIMESTAMPDIFF(DAY, '2018-01-15 11:30:20', '2018-01-20 13:40:30') |
+------------------------------------------------------------------+
| 5 |
+------------------------------------------------------------------+
mysql> SELECT TIMESTAMPDIFF(MONTH, '2018-01-15 11:30:20', '2018-03-20 13:40:30');
+--------------------------------------------------------------------+
| TIMESTAMPDIFF(MONTH, '2018-01-15 11:30:20', '2018-03-20 13:40:30') |
+--------------------------------------------------------------------+
| 2 |
+--------------------------------------------------------------------+
mysql> SELECT TIMESTAMPDIFF(YEAR, '2018-01-15 11:30:20', '2020-01-20 13:40:30');
+-------------------------------------------------------------------+
| TIMESTAMPDIFF(YEAR, '2018-01-15 11:30:20', '2020-01-20 13:40:30') |
+-------------------------------------------------------------------+
| 2 |
+-------------------------------------------------------------------+変換
DATE_FORMAT｜指定書式に変換
mysql> SELECT DATE_FORMAT('2018-01-15 11:30:20', '%H:%i:%s');
+------------------------------------------------+
| DATE_FORMAT('2018-01-15 11:30:20', '%H:%i:%s') |
+------------------------------------------------+
| 11:30:20 |
+------------------------------------------------+
mysql> SELECT DATE_FORMAT('2018-01-15 11:30:20', '%Y/%m/%d');
+------------------------------------------------+
| DATE_FORMAT('2018-01-15 11:30:20', '%Y/%m/%d') |
+------------------------------------------------+
| 2018/01/15 |
+------------------------------------------------+TIME_TO_SEC｜秒に変換
mysql> SELECT TIME_TO_SEC('00:02:02');
+-------------------------+
| TIME_TO_SEC('00:02:02') |
+-------------------------+
| 122 |
+-------------------------+CONVERT_TZ｜タイムゾーンを変換
mysql> SELECT CONVERT_TZ('2018-01-15 12:00:00', "+00:00", "+09:00");
+-------------------------------------------------------+
| CONVERT_TZ('2018-01-15 12:00:00', "+00:00", "+09:00") |
+-------------------------------------------------------+
| 2018-01-15 21:00:00 |
+-------------------------------------------------------+UNIX_TIMESTAMP｜UNIXタイムスタンプに変換
mysql> SELECT UNIX_TIMESTAMP('2018-01-15 11:30:20');
+---------------------------------------+
| UNIX_TIMESTAMP('2018-01-15 11:30:20') |
+---------------------------------------+
| 1515983420 |
+---------------------------------------+FROM_UNIXTIME｜UNIXタイムスタンプを日時に変換
mysql> SELECT FROM_UNIXTIME(1515983420);
+---------------------------+
| FROM_UNIXTIME(1515983420) |
+---------------------------+
| 2018-01-15 11:30:20 |
+---------------------------+
