Mysql常见的日期、时间函数

本文链接：https://blog.csdn.net/qq_45464560/article/details/119754623

Mysql常见的日期、时间函数

一、日期（date）相关

CURDATE()、CURRENT_DATE()、CURRENT_DATE
同义，返回当前日期，如：

mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE;
+------------+----------------+--------------+
| CURDATE()  | CURRENT_DATE() | CURRENT_DATE |
+------------+----------------+--------------+
| 2021-08-17 | 2021-08-17     | 2021-08-17   |
+------------+----------------+--------------+
1 row in set (0.00 sec)

二、时间（time）相关

CURTIME()、CURRENT_TIME()、CURRENT_TIME
同义，返回当前时间，返回值以当前时区表达。 如：

mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME;
+-----------+----------------+--------------+
| CURTIME() | CURRENT_TIME() | CURRENT_TIME |
+-----------+----------------+--------------+
| 14:06:15  | 14:06:15       | 14:06:15     |
+-----------+----------------+--------------+
1 row in set (0.00 sec)

三、日期和时间

1、NOW()、CURRENT_TIMESTAMP()、CURRENT_TIMESTAMP、LOCALTIME()、LOCALTIME、LOCALTIMESTAMP()、LOCALTIMESTAMP
同义，返回当前日期和时间。 如：

mysql> SELECT NOW(),CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,LOCALTIME(),LOCALTIME,LOCALTIMESTAMP(),LOCALTIMESTAMP;
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| NOW()               | CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP   | LOCALTIME()         | LOCALTIME           | LOCALTIMESTAMP()    | LOCALTIMESTAMP      |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2021-08-17 13:57:50 | 2021-08-17 13:57:50 | 2021-08-17 13:57:50 | 2021-08-17 13:57:50 | 2021-08-17 13:57:50 | 2021-08-17 13:57:50 | 2021-08-17 13:57:50 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

2、SYSDATE()
SYSDATE()函数接受一个可选参数fsp，它确定结果是否应该包含从0到6的小数秒精度。
如果不传参数：

mysql> SELECT SYSDATE();
+---------------------+
| SYSDATE()           |
+---------------------+
| 2021-08-17 14:11:28 |
+---------------------+
1 row in set (0.00 sec)

如果你传递fsp参数，则结果将包括小数秒精度，如以下示例所示

mysql> SELECT SYSDATE(6);
+----------------------------+
| SYSDATE(6)                 |
+----------------------------+
| 2021-08-17 14:12:22.044852 |
+----------------------------+
1 row in set (0.00 sec)

3、SYSDATE 与 NOW 比较
请考虑以下示例：

mysql> SELECT SYSDATE(), NOW();
+---------------------+---------------------+
| SYSDATE()           | NOW()               |
+---------------------+---------------------+
| 2021-08-17 14:14:21 | 2021-08-17 14:14:21 |
+---------------------+---------------------+
1 row in set (0.00 sec)

SYSDATE()和NOW()函数都返回一个相同的值？其实不然
SYSDATE()： 返回执行当前函数的时间
NOW()： 返回执行当前sql语句的时间
请考虑以下示例：

mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2021-08-17 14:18:35 |        0 | 2021-08-17 14:18:35 |
+---------------------+----------+---------------------+
1 row in set (2.03 sec)

mysql> SELECT SYSDATE(),SLEEP(2),SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2021-08-17 14:20:24 |        0 | 2021-08-17 14:20:26 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

仔细观察我们会发现
第一条SQL我们查询的是NOW()，中间休眠了两秒，最后两个NOW()查询的时间是一样的；
第二条SQL我们查询的是SYSDATE()，中间休眠了两秒，结果后面的SYSDATE()确实要比前面的推迟两秒
得出结论：NOW()返回执行当前SQL语句的时间；SYSDATE()返回执行当前函数的时间。

四、日期与时间的比较及运算

1、日期和时间的比较

SELECT * FROM charging_rules WHERE startTime <= '14:00:00' AND endTime >= '18:00:00'

SELECT * FROM charging_rules WHERE startTime <= '2019-03-21' AND endTime >= '2019-03-21'

SELECT * FROM charging_rules WHERE startTime <= '2019-03-21 14:00:00' AND endTime >= '2019-03-21 14:00:00'

总结:
Mysql中可以直接用字符串进行比较时间大小，只要你的日期格式是合法的就行。
startTime 和 endTime 字段类型都为varchar的，也可以直接进行比较大小。
当然这字段用日期类型 datetime 来进行比较，也是可以的。

2、日期和时间的运算
date_add()： MySQL 为时间增加一个时间间隔
示例：

mysql> SELECT date_add(now(),interval 1 day)
    -> ,date_add(now(),interval 1 hour)
    -> ,date_add(now(),interval 1 minute)
    -> ,date_add(now(),interval 1 second)
    -> ,date_add(now(),interval 1 week)
    -> ,date_add(now(),interval 1 month)
    -> ,date_add(now(),interval 1 quarter)
    -> ,date_add(now(),interval 1 year);
+--------------------------------+---------------------------------+-----------------------------------+-----------------------------------+---------------------------------+----------------------------------+------------------------------------+---------------------------------+
| date_add(now(),interval 1 day) | date_add(now(),interval 1 hour) | date_add(now(),interval 1 minute) | date_add(now(),interval 1 second) | date_add(now(),interval 1 week) | date_add(now(),interval 1 month) | date_add(now(),interval 1 quarter) | date_add(now(),interval 1 year) |
+--------------------------------+---------------------------------+-----------------------------------+-----------------------------------+---------------------------------+----------------------------------+------------------------------------+---------------------------------+
| 2021-08-19 13:50:57            | 2021-08-18 14:50:57             | 2021-08-18 13:51:57               | 2021-08-18 13:50:58               | 2021-08-25 13:50:57             | 2021-09-18 13:50:57              | 2021-11-18 13:50:57                | 2022-08-18 13:50:57             |
+--------------------------------+---------------------------------+-----------------------------------+-----------------------------------+---------------------------------+----------------------------------+------------------------------------+---------------------------------+
1 row in set (0.00 sec)

set @dt = now();
select date_add(@dt, interval 1 day); - 加1天
select date_add(@dt, interval 1 hour); -加1小时
select date_add(@dt, interval 1 minute); - 加1分钟
select date_add(@dt, interval 1 second); -加1秒
select date_add(@dt, interval 1 microsecond); -加1毫秒
select date_add(@dt, interval 1 week); -加1周
select date_add(@dt, interval 1 month); -加1月
select date_add(@dt, interval 1 quarter); -加1季
select date_add(@dt, interval 1 year); -加1年

同时interval 后面是可以支持负数的，也就是说date_add既可以加也可以减

datediff(date1,date2), timediff(time1,time2)： 日期、时间相减函数

datediff(date1,date2)：两个日期相减 date1 date2，返回天数
请看如下示例：

mysql> select datediff('2021-08-18', '2021-07-01');
+--------------------------------------+
| datediff('2021-08-18', '2021-07-01') |
+--------------------------------------+
|                                   48 |
+--------------------------------------+
1 row in set (0.04 sec)

mysql> select datediff('2021-07-01','2021-08-18' );
+--------------------------------------+
| datediff('2021-07-01','2021-08-18' ) |
+--------------------------------------+
|                                  -48 |
+--------------------------------------+
1 row in set (0.00 sec)

MySQL timediff(time1,time2)：两个日期相减 time1 time2，返回 time 差值。
请看如下示例：

mysql> select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');
+--------------------------------------------------------+
| timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00') |
+--------------------------------------------------------+
| 08:08:08                                               |
+--------------------------------------------------------+
1 row in set (0.03 sec)

mysql> select timediff('2008-08-08 08:08:08', '2008-08-07 00:00:00');
+--------------------------------------------------------+
| timediff('2008-08-08 08:08:08', '2008-08-07 00:00:00') |
+--------------------------------------------------------+
| 32:08:08                                               |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('2008-08-06 00:00:00', '2008-08-07 00:00:00');
+--------------------------------------------------------+
| timediff('2008-08-06 00:00:00', '2008-08-07 00:00:00') |
+--------------------------------------------------------+
| -24:00:00                                              |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select timediff('08:08:08', '00:00:00');
+----------------------------------+
| timediff('08:08:08', '00:00:00') |
+----------------------------------+
| 08:08:08                         |
+----------------------------------+
1 row in set (0.00 sec)

五、日期和时间的格式化

DATE_FORMAT(date,format)
用format 格式化date，format 为格式化字符串，常用的部分格式化标识符为：
%Y：年，4位；

%y：年，2位；

%M：月，月名称，如January；

%m：月，2位（01-12）；

%D：日，如1st，2nd；

%d：日，2位（01-31）；

%H：时，2位，24小时制；

%h：时，12小时制；

%i：分，2位（00-59）；

%S或%s：秒，2位（00-59）；

%p：PM 或AM；

%r：HH:mm:ss PM形式时间；

%W：周，周名，如Tuesday；

%w：周，0-6，0为周日；

%j：当前日期所在一年中的天数，3位（001-366）。

请考虑以下示例：
同时格式化日期和时间

mysql> SELECT DATE_FORMAT(SYSDATE(),"%Y-%m-%d %H:%i:%s");
+--------------------------------------------+
| DATE_FORMAT(SYSDATE(),"%Y-%m-%d %H:%i:%s") |
+--------------------------------------------+
| 2021-08-17 14:41:46                        |
+--------------------------------------------+
1 row in set (0.00 sec)

只格式化日期

mysql> SELECT DATE_FORMAT(SYSDATE(),"%Y-%m-%d");
+-----------------------------------+
| DATE_FORMAT(SYSDATE(),"%Y-%m-%d") |
+-----------------------------------+
| 2021-08-17                        |
+-----------------------------------+
1 row in set (0.00 sec)

只格式化时间

mysql> SELECT DATE_FORMAT(SYSDATE(),"%H:%i:%s");
+-----------------------------------+
| DATE_FORMAT(SYSDATE(),"%H:%i:%s") |
+-----------------------------------+
| 14:42:31                          |
+-----------------------------------+
1 row in set (0.00 sec)