目录
- 1、日期函数
- 2、时间函数
- 3、日期时间函数
- 3.1、
CURRENT_TIMESTAMP() - 3.2、
CURRENT_TIMESTAMP - 3.3、`LOCALTIME()
- 3.4、
LOCALTIME - 3.5、
LOCALTIMESTAMP() - 3.6、
LOCALTIMESTAMP - 3.7、
NOW() - 3.8、
SYSDATE() - 4、日期时间比较运算
- 5、日期时间运算
- 6、日期转字符串
- 7、字符串转日期
- 8、常用特殊日期
1、日期函数
CURDATE()、CURRENT_DATE()、CURRENT_DATE均可获取当前日期
1.1、CURDATE()
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2022-09-01 |
+----------------+
1 row in set (0.00 sec)
mysql>
1.2、CURRENT_DATE()
mysql> SELECT CURRENT_DATE();
+----------------+
| CURRENT_DATE() |
+----------------+
| 2022-09-01 |
+----------------+
1 row in set (0.00 sec)
mysql>
1.3、CURRENT_DATE
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2022-09-01 |
+--------------+
1 row in set (0.00 sec)
mysql>
1.4、将日期转为19900101格式
mysql> SELECT CURRENT_DATE + 0 AS result;
+----------+
| result |
+----------+
| 20220901 |
+----------+
1 row in set (0.00 sec)
mysql>
2、时间函数
CURTIME(), CURRENT_TIME(), CURRENT_TIME均可获取时间
2.1、CURTIME()
mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 17:13:25 |
+-----------+
1 row in set (0.00 sec)
mysql>
2.2、CURRENT_TIME()
mysql> SELECT CURRENT_TIME();
+----------------+
| CURRENT_TIME() |
+----------------+
| 17:13:25 |
+----------------+
1 row in set (0.00 sec)
mysql>
2.3、CURRENT_TIME
mysql> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 17:13:25 |
+--------------+
1 row in set (0.00 sec)
mysql>
3、日期时间函数
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIME, LOCALTIMESTAMP(), LOCALTIMESTAMP, NOW(), SYSDATE()均可获取日期时间。
3.1、CURRENT_TIMESTAMP()
mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.2、CURRENT_TIMESTAMP
mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.3、`LOCALTIME()
mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME() |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.4、LOCALTIME
mysql> SELECT LOCALTIME;
+---------------------+
| LOCALTIME |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.5、LOCALTIMESTAMP()
mysql> SELECT 5、LOCALTIMESTAMP();
+---------------------+
| 5、LOCALTIMESTAMP() |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.6、LOCALTIMESTAMP
mysql> SELECT LOCALTIMESTAMP;
+---------------------+
| LOCALTIMESTAMP |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.7、NOW()
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
3.8、SYSDATE()
mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE() |
+---------------------+
| 2022-09-01 17:23:21 |
+---------------------+
1 row in set (0.00 sec)
mysql>
4、日期时间比较运算
MySQL约定只要是合法的日期格式，就能比较
mysql> SELECT CURRENT_TIMESTAMP() > '2022-09-01';
+------------------------------------+
| CURRENT_TIMESTAMP() > '2022-09-01' |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP() > '20220901';
+----------------------------------+
| CURRENT_TIMESTAMP() > '20220901' |
+----------------------------------+
| 1 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_TIMESTAMP() > '2022-09-01 00:00:00';
+---------------------------------------------+
| CURRENT_TIMESTAMP() > '2022-09-01 00:00:00' |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql>
5、日期时间运算
5.1、日期增加
DATE_ADD(date,INTERVAL expr type)函数向日期添加指定的时间间隔。其中date参数是合法的日期表达式，expr参数是您希望添加的时间间隔，可选值为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
当expr为正数时，表示增加，否则为减少。
5.1、日期减少
DATE_SUB(date,INTERVAL expr type)函数向日期减去指定的时间间隔。其中date参数是合法的日期表达式，expr参数是您希望减去的时间间隔，可选值为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
当expr为正数时，表示减去，否则为增加。
6、日期转字符串
日期格式化借助函数DATE_FORMAT(date, format)，format格式常用%Y-%m-%d %H:%i:%s
mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i');
+--------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') |
+--------------------------------------+
| 2022-09-01 17:34 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>
7、字符串转日期
字符串转日期DATETIME类型借助函数STR_TO_DATE(str, format)，format格式常用%Y-%m-%d %H:%i:%s
mysql> SELECT STR_TO_DATE('2012-12-20 19:19', '%Y-%m-%d %H:%i:%s');
+------------------------------------------------------+
| STR_TO_DATE('2012-12-20 19:19', '%Y-%m-%d %H:%i:%s') |
+------------------------------------------------------+
| 2012-12-20 19:19:00 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
8、常用特殊日期
8.1、取本月第一天，如下两种方式都可以
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY);
+-------------------------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL -DAY(CURDATE()) + 1 DAY) |
+-------------------------------------------------------+
| 2022-09-01 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01');
+------------------------------------------------+
| CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m'), '-01') |
+------------------------------------------------+
| 2022-09-01 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>