MySQL日期相关

目录

1.日期函数

1.1获取时间

1.2日期加减

1.2.1DATE_ADD:为日期加一个时间间隔

1.2.2DATE_SUB:为日期减一个时间间隔 

1.2.3ADDTIME:为日期增加秒数

 1.2.4ADDDATE:为日期增加日期

1.3日期差值

1.3.1DATEDIFF

1.3.2TIMESTAMPDIFF

2.特殊日期获取

2.1上月月初

 2.2上月月底

2.3本月月初

2.4本月月底

2.5下月月初

2.6下月月底

3.日期格式转换 

3.1DATE_FORMAT函数

3.2 字符转日期STR_TO_DATE

3.3数字转日期

 4.日期和时间数据类型

---

1.日期函数

1.1获取时间

MySQL提供了多种方法来获取当前日期、时间和时间戳

以下是一些常用的MySQL时间函数

函数	日期格式
NOW()	YYYY-MM-DD hh:mm:ss
CURDATE()	YYYY-MM-DD
CURTIME()	hh:mm:ss
CURRENT_TIMESTAMP()	YYYY-MM-DD hh:mm:ss

实例：当前日期时间

SELECT NOW()

结果：

2024-08-07 03:13:50

1.2日期加减

Mysql中日期时间的加减，可以使用DATE_ADD(),DATE_SUB()，ADDTIME(),ADDDATE()等函数

1.2.1DATE_ADD:为日期加一个时间间隔

语法：

DATE_ADD(date,INTERVAL expr type)

其中

• date：指定的日期时间
• expr ：要加的时间间隔
• type：时间间隔单位

实例：日期增加1天

SELECT DATE_ADD('2024-08-07',INTERVAL 1 day) 

结果：

2024-08-08

实例：日期加1年

SELECT DATE_ADD('2024-08-07',INTERVAL 1 year) 

结果： 

 2025-08-07

1.2.2DATE_SUB:为日期减一个时间间隔 

语法：

DATE_SUB(date,INTERVAL expr type)

其中

• date：指定的日期时间
• expr ：要减的时间间隔
• type：时间间隔单位

实例：日期减一天

SELECT DATE_SUB('2024-08-07',INTERVAL 1 day) 

结果： 

 2024-08-06 

1.2.3ADDTIME:为日期增加秒数

语法：

ADDTIME(date,expr )

其中

• date：指定的日期时间
• expr ：要加的秒数(小于60)

实例：日期加17秒

SELECT ADDTIME('2024-08-07 03:13:50',17) 

结果：

2024-08-07 03:14:07 

 1.2.4ADDDATE:为日期增加日期

语法：

ADDDATE(date[,INTERVAL] expr [type])

其中

• date：指定的日期时间
• expr ：要加的时间间隔
• type：时间间隔单位

当只有expr参数时表示增加指定天数；当三个参数都存在时，表示增加指定时间间隔

实例：日期加1天

SELECT ADDDATE('2024-08-07 03:13:50',1) 

结果：

2024-08-08 03:13:50

实例：日期加 4个月

SELECT ADDDATE('2024-08-07 03:13:50',INTERVAL 4 month) 

结果：

2024-12-07 03:13:50

1.3日期差值

1.3.1DATEDIFF

MySQL用DATEDIFF来计算2个日期之间的相差天数

语法：

DATEDIFF(date1,date2)

其中，date1和date2是要进行比较的两个日期。DATEDIFF函数会返回date1减去date2得到天值差数

实例：

SELECT DATEDIFF('2024-08-07','2024-08-01') ,DATEDIFF('2024-08-01','2024-08-07') 

 结果：

6，-6

1.3.2TIMESTAMPDIFF

在MySQL中，可以使用TIMESTAMPDIFF函数来求出两个日期之间相差间隔时间

语法：

TIMESTAMPDIFF(unit,date1,date2)

其中，unit是时间单位，可以是SECOND、MINUTE、HOUR、DAY等；date1和date2是要进行比较的两个日期或时间戳。

实例：

SELECT TIMESTAMPDIFF(SECOND,'2024-03-13 05:30:13','2024-05-30 04:28:23')

 结果：

6735490

2.特殊日期获取

2.1上月月初

实例：

SELECT DATE_SUB(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 day),INTERVAL 1 month)

 结果：

2024-07-01

 2.2上月月底

实例：

SELECT DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE()) day)

结果：

2024-07-31 

2.3本月月初

实例：

SELECT DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 day)

结果：

2024-08-01  

2.4本月月底

实例：

SELECT  DATE_SUB(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 day),INTERVAL 1 month),INTERVAL 1 day)

结果：

2024-08-31

2.5下月月初

实例：

SELECT  DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 day),INTERVAL 1 month)

结果：

2024-09-01 

2.6下月月底

实例：

SELECT  DATE_SUB(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 day),INTERVAL 2 month),INTERVAL 1 day)

结果：

2024-09-30

3.日期格式转换 

3.1DATE_FORMAT函数

MySQL可以通过DATE_FORMAT函数将日期转成想要的格式

语法：

DATE_FORMAT(date,format)

其中，date是要转换的日期，format是转成的格式 

format日期时间格式化符号示例：

符号	格式
%Y	四位年份
%y	两位年份
%m	两位月份
%c	月份（不会有0的前缀）
%d	两位日期
%H	二十四小时制小时（00-23）
%h	十二小时制小时（01-12）
%i	两位分钟
%s	两位秒数
%p	AM或PM

实例：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') 

结果：

2024-08-07 03:13:50 

3.2 字符转日期STR_TO_DATE

MySQL中的STR_TO_DATE函数可以将一个字符串转换为日期类型。

语法：

STR_TO_DATE(str,format)

其中，str是要转换成日期的字符串，format表示输入字符串str的日期格式。若str符合format格式，择会将str转换成日期，否则返回NULL,可以由此判断字符是否可以转成日期

实例：

SELECT STR_TO_DATE('2024-09-01', '%Y-%m-%d'),STR_TO_DATE('2024-09-01', '%Y%m%d')

 结果：

2024-09-01，NULL

3.3数字转日期

数字可以先转成字符，再通过STR_TO_DATE函数转成日期

实例：

SELECT STR_TO_DATE(convert(20240901,CHAR), '%Y%m%d')

结果：

2024-09-01

 4.日期和时间数据类型

MySQL常见日期类型有

日期类型	格式	范围
DATE	YYYY-MM-DD	'1000-01-01' to '9999-12-31'
TIME	hh:mm:ss	'-838:59:59.000000' to '838:59:59.000000'
DATETIME	YYYY-MM-DD hh:mm:ss	'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999'
TIMESTAMP	YYYY-MM-DD hh:mm:ss	‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC
YEAR	YYYY	1901 ~ 2155

 一般建表时候，创建时间用datetime，更新时间用timestamp。

MySQL默认自动给第一个timestamp字段加上DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP对日期进行自动更新，第二个及之后的timestamp字段则没有这个默认条件。若想取消这个条件，可以使用NULL DEFAULT NULL。