日付・時間関数
現在の日付・時刻を取得する関数
CURDATE(), CURRENT_DATE()
mysql> SELECT CURDATE();
+------------+
| CURDATE() |
+------------+
| 2018-08-04 |
+------------+
1 row in set (0.01 sec)
CURTIME(), CURRENT_TIME()
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 19:02:43 |
+-----------+
1 row in set (0.00 sec)
NOW()
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2018-08-04 19:03:03 |
+---------------------+
1 row in set (0.01 sec)
UTCDATE(), UTCTIME(), UTC_TIMESTAMP()
mysql> SELECT UTC_TIME();
+------------+
| UTC_TIME() |
+------------+
| 19:03:42 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT UTC_DATE();
+------------+
| UTC_DATE() |
+------------+
| 2018-08-04 |
+------------+
1 row in set (0.02 sec)
mysql> SELECT UTC_TIMESTAMP();
+---------------------+
| UTC_TIMESTAMP() |
+---------------------+
| 2018-08-04 19:04:21 |
+---------------------+
1 row in set (0.00 sec)
SYSDATE()
mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE() |
+---------------------+
| 2018-08-04 19:04:46 |
+---------------------+
1 row in set (0.00 sec)
部分的な取り出し関数
YEAR()
mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
| 2018 |
+-------------+
1 row in set (0.00 sec)
MONTH()
mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
| 8 |
+--------------+
1 row in set (0.00 sec)
WEEK()
mysql> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
| 30 |
+-------------+
1 row in set (0.00 sec)
DAY()
mysql> SELECT DAY(NOW());
+------------+
| DAY(NOW()) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
HOUR()
mysql> SELECT HOUR(NOW());
+-------------+
| HOUR(NOW()) |
+-------------+
| 19 |
+-------------+
1 row in set (0.03 sec)
MINUTE()
mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
| 9 |
+---------------+
1 row in set (0.00 sec)
SECOND()
mysql> SELECT SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
| 20 |
+---------------+
1 row in set (0.00 sec)
間隔の計算
DATEADD(), DATESUB()
mysql> SELECT DATE_ADD('2018-08-04 19:13:00', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2018-08-04 19:13:00', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-08-05 19:13:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2018-08-04 19:13:00', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_SUB('2018-08-04 19:13:00', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2018-08-03 19:13:00 |
+-------------------------------------------------+
1 row in set (0.02 sec)
ADDTIME(), SUBTIME()
mysql> SELECT ADDTIME('2018-08-04 19:13:00', '01:13:00');
+--------------------------------------------+
| ADDTIME('2018-08-04 19:13:00', '01:13:00') |
+--------------------------------------------+
| 2018-08-04 20:26:00 |
+--------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF(), TIMEDIFF()
mysql> SELECT DATEDIFF('2018-08-04 19:13:00', NOW());
+----------------------------------------+
| DATEDIFF('2018-08-04 19:13:00', NOW()) |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIMEDIFF('2018-08-04 19:13:00', NOW());
+----------------------------------------+
| TIMEDIFF('2018-08-04 19:13:00', NOW()) |
+----------------------------------------+
| -00:03:03 |
+----------------------------------------+
1 row in set (0.00 sec)