日付関数(比較, 加算, 差分, 抽出)の使い方

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 = Tuesday6 = 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 |
+---------------------------------------+