MySQL常用日期函数

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>

posted @ 2022-09-01 17:52 Bruce.Chang.Lee 阅读(484) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MySQL常用日期函数

1、日期函数

1.1、CURDATE()

1.2、CURRENT_DATE()

1.3、CURRENT_DATE