MySQL的时间差函数、日期转换计算函数

MySQL的时间差函数(TIMESTAMPDIFF、DATEDIFF)、日期转换计算函数(date_add、day、date_format、str_to_date)

1. 时间差函数（TIMESTAMPDIFF、DATEDIFF）

需要用MySQL计算时间差，使用TIMESTAMPDIFF、DATEDIFF，记录一下实验结果

--0
select datediff(now(), now());--2
select datediff('2023-09-22 23:59:00', '2023-09-20 00:00:00');--2
select datediff('2023-09-22 00:00:00', '2023-09-20 23:59:00');--1
select TIMESTAMPDIFF(DAY, '2023-09-20 23:59:00', '2023-09-22 00:00:00');--2
select TIMESTAMPDIFF(DAY, '2023-09-20 00:00:00', '2023-09-22 00:00:00');--2
select TIMESTAMPDIFF(DAY, '2023-09-20 00:00:00', '2023-09-22 12:00:00');--2
select TIMESTAMPDIFF(DAY, '2023-09-20 00:00:00', '2023-09-22 23:59:00');--71
select TIMESTAMPDIFF(HOUR, '2023-09-20 00:00:00', '2023-09-22 23:00:00');--4260
select TIMESTAMPDIFF(MINUTE, '2023-09-20 00:00:00', '2023-09-22 23:00:00');

2. 日期转换计算函数（date_add、day、date_format、str_to_date）

DATE_ADD() 函数使用

定义和用法：DATE_ADD() 函数向日期添加指定的时间间隔。

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值：DAY ，WEEK 等

DATE_ADD("2023-09-20 ",INTERVAL 2 day)结果："2023-09-22 "

DATE_ADD("2023-09-20 ",INTERVAL -2 day)结果："2023-09-18 "

date_format()：返回格式化后的时间日期

语法：

DATE_FORMAT(date,format);

参数：

date：需要格式化的有效时间日期

format：预定义格式的字符串，每个说明符前面都有一个百分比号(%).

SELECT id,name,DATE_FORMAT(start_time, '%Y-%m-%d %H:%i:%S') start_time,DATE_FORMAT(end_time, '%Y-%m-%d %H:%i:%S')   end_time
FROM user

STR_TO_DATE函数用于将字符串转变为日期/时间值

STR_TO_DATE函数与DATE_FORMAT函数的作用是相反的

语法格式

STR_TO_DATE(str,format)

srt:要格式化为日期的字符串(输入字符串)

format:要使用的格式字符串

如果不能按照format解析str，STR_TO_DATE函数将返回NULL

如果其中任何一个参数为NULL，STR_TO_DATE函数将返回NULL

预定义说明字符列表：

常用的的format格式

练习案例
将字符串转换为DATE值

-- 2023-09-20
SELECT STR_TO_DATE('20,9,2023','%d,%m,%Y');
-- 2023-09-20
SELECT STR_TO_DATE('2023-09-20 11:30:00','%Y-%m-%d');
-- 2023-08-01
SELECT STR_TO_DATE('August,1,2023', '%M,%e,%Y');
-- 2023-08-10
SELECT STR_TO_DATE('August 10 2023', '%M %d %Y');
-- 2023-08-15
SELECT STR_TO_DATE('Monday, August 15, 2023', '%W,%M %e, %Y');将字符串转换为DATETIME值 -- 2023-09-20 11:30:00
SELECT STR_TO_DATE('20230920 1130','%Y%m%d %h%i');
-- 2023-09-20 11:30:00
SELECT STR_TO_DATE('2023-09-20 11:30:00','%Y-%m-%d %H:%i:%s');
-- 2023-09-20 10:40:10
SELECT STR_TO_DATE('2023,9,20 10,40,10', '%Y,%m,%d %h,%i,%s');
STR_TO_DATE函数在根据格式字符串format解析输入字符串str时，忽略输入字符串str末尾的额外字符-- 2023-09-20
SELECT STR_TO_DATE('20,9,2023 extra characters','%d,%m,%Y'); 
-- 2023-09-20 11:30:00
SELECT STR_TO_DATE('20230920 1130 extra characters','%Y%m%d %h%i');
如果输入字符串str是非法的，则STR_TO_DATE函数返回NULL-- NULL
SELECT STR_TO_DATE('2023','%Y');
-- NULL
SELECT STR_TO_DATE('11','%h');
-- NULL
SELECT STR_TO_DATE('1130','%h%i');
-- NULL
SELECT STR_TO_DATE('113005','%h%i%s');
-- NULL
SELECT STR_TO_DATE('August,20,2023', '%M %e %Y');
-- NULL
SELECT STR_TO_DATE('Monday, August 20, 2023', '%W %M %e %Y');

-- 用日期与字符串转换，计算当月第一天、下月第一天
select curdate() as '当前日期', 
DATE_FORMAT(curdate(), '%Y-%m') as '当前月份', 
str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d') as '当前月的第一天', 
date_add(str_to_date(concat(DATE_FORMAT(curdate(), '%Y-%m'), '-01'), '%Y-%m-%d'), interval 1 month) as '下月的第一天';-- 当前月的最后一天
select last_day(curdate());-- 下月第一天
select date_add(last_day(curdate()), interval 1 day);-- 当天为当月的第几天
select day(curdate());-- 当月第一天
select date_add(curdate(), interval 1-(day(curdate())) day);