Mysql内置函数-时间处理函数

时间处理函数

系统获取

CurDate() 返回当前日期

select CurDate();
+------------+
| CurDate()  |
+------------+
| 2024-10-22 |
+------------+
1 row in set (0.00 sec)

CurTime() 返回当前时间

select CurTime();
+-----------+
| CurTime() |
+-----------+
| 05:18:16  |
+-----------+
1 row in set (0.00 sec)

Now() 返回当前日期+时间

select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-10-22 05:36:34 |
+---------------------+
1 row in set (0.00 sec)

时间计算

DateDiff(str,str) 计算日期差值

select Datediff('2024-10-11','2024-10-01');
+-------------------------------------+
| Datediff('2024-10-11','2024-10-01') |
+-------------------------------------+
|                                  10 |
+-------------------------------------+
1 row in set (0.00 sec)

Date_Add(str,INTERVAL ±num DAY)

select date_add('2022-03-23',INTERVAL 7 DAY);
+---------------------------------------+
| date_add('2022-03-23',INTERVAL 7 DAY) |
+---------------------------------------+
| 2022-03-30                            |
+---------------------------------------+
1 row in set (0.00 sec)

select date_add('2022-03-23',INTERVAL -7 DAY);
+----------------------------------------+
| date_add('2022-03-23',INTERVAL -7 DAY) |
+----------------------------------------+
| 2022-03-16                             |
+----------------------------------------+
1 row in set (0.00 sec)

AddDate(str,num) 增加天

select ADDDATE(CURDATE(),20);
+-----------------------+
| ADDDATE(CURDATE(),20) |
+-----------------------+
| 2024-11-11            |
+-----------------------+
1 row in set (0.00 sec)

mysql> select ADDDATE(NOW(),20);
+---------------------+
| ADDDATE(NOW(),20)   |
+---------------------+
| 2024-11-11 05:41:54 |
+---------------------+
1 row in set (0.00 sec)

AddTime(str,num) 增加小时

select ADDTIME(NOW(),20);
+---------------------+
| ADDTIME(NOW(),20)   |
+---------------------+
| 2024-10-22 05:42:40 |
+---------------------+
1 row in set (0.00 sec)

mysql> select ADDTIME(CURDATE(),20);
+-----------------------+
| ADDTIME(CURDATE(),20) |
+-----------------------+
| 2024-10-22 00:00:20   |
+-----------------------+
1 row in set (0.00 sec)

Date_Format(str,format) 日期格式化

select date_format('2022-03-23',"%Y/%m/%d");
+--------------------------------------+
| date_format('2022-03-23',"%Y/%m/%d") |
+--------------------------------------+
| 2022/03/23                           |
+--------------------------------------+
1 row in set (0.00 sec)

从col或字符串获取

Date(str) 返回时间的日期部分

select Date('2024-10-11:10:01:01');
+-----------------------------+
| Date('2024-10-11:10:01:01') |
+-----------------------------+
| 2024-10-11                  |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Day(str) 返回日期的天

date的格式全部兼容

select day('20240102');
+-----------------+
| day('20240102') |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

Dayofweek(str) 返回星期几

select dayofweek('20240102');
+-----------------------+
| dayofweek('20240102') |
+-----------------------+
|                     3 |
+-----------------------+
1 row in set (0.00 sec)

Hour(str) 返回小时

格式兼容有限’2023-10-11 05:33:46’，05:33:46

select curtime();
+-----------+
| curtime() |
+-----------+
| 05:33:46  |
+-----------+
1 row in set (0.00 sec)

mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

select hour('2023-10-11 05:33:46');
+-----------------------------+
| hour('2023-10-11 05:33:46') |
+-----------------------------+
|                           5 |
+-----------------------------+
1 row in set (0.00 sec)

Minute(str) 返回分钟部分

select MINUTE('2023-10-11 05:33:46');
+-------------------------------+
| MINUTE('2023-10-11 05:33:46') |
+-------------------------------+
|                            33 |
+-------------------------------+

Month(str) 返回月部分

select MONTH('2023-10-11 05:33:46');
+------------------------------+
| MONTH('2023-10-11 05:33:46') |
+------------------------------+
|                           10 |
+------------------------------+
1 row in set (0.00 sec)

Second(str) 返回时间的秒部分

select SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
|            55 |
+---------------+

Time(str) 返回时间部分

select TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 05:37:16    |
+-------------+

Year(str) 返回年部分

select YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2024 |
+-------------+
1 row in set (0.00 sec)