MySQL计算两个日期相差天数

MySQL 计算两个日期之间的天数-- 1.利用TO_DAYS函数select to_days(now()) - to_days('20120512')-- 2.利用DATEDIFF函数select datediff(now(),'20120512')TIMESTAMPDIFF函数可用于计算两日期时间之间相差的天数，秒数，分钟数，周数，小时数语法：TIMESTAMPDIFF(unit,dateti

A_Big_Banana

18417人浏览 · 2022-01-13 16:47:47

A_Big_Banana · 2022-01-13 16:47:47 发布

日常开发中使用mysql数据库，一定会用到的操作就是时间相关的比较。
经常会有查询今天、昨天、前七天、前30天等查询条件。
简单且准确的时间计算希望能帮到你

MySQL 计算两个日期之间的天数

TO_DAYS函数

SELECT
	TO_DAYS('2022-12-12 00:00:00') - TO_DAYS('2022-11-11 00:00:00') d1,
	TO_DAYS('2022-12-12 12:12:12') - TO_DAYS('2022-11-11 00:00:00') d2,
	TO_DAYS('2022-12-12 23:59:59') - TO_DAYS('2022-11-11 00:00:00') d3,
	
	TO_DAYS('2022-12-12 00:00:00') - TO_DAYS('2022-11-11 11:11:11') d4,
	TO_DAYS('2022-12-12 12:12:12') - TO_DAYS('2022-11-11 11:11:11') d5,
	TO_DAYS('2022-12-12 23:59:59') - TO_DAYS('2022-11-11 11:11:11') d6,
	
	TO_DAYS('2022-12-12 00:00:00') - TO_DAYS('2022-11-11 23:59:59') d7,
	TO_DAYS('2022-12-12 12:12:12') - TO_DAYS('2022-11-11 23:59:59') d8,
	TO_DAYS('2022-12-12 23:59:59') - TO_DAYS('2022-11-11 23:59:59') d9

在这里插入图片描述

DATEDIFF函数

SELECT
	DATEDIFF('2022-12-12 00:00:00','2022-11-11 00:00:00') d1,
	DATEDIFF('2022-12-12 12:12:12','2022-11-11 00:00:00') d2,
	DATEDIFF('2022-12-12 23:59:59','2022-11-11 00:00:00') d3,
	
	DATEDIFF('2022-12-12 00:00:00','2022-11-11 11:11:11') d4,
	DATEDIFF('2022-12-12 12:12:12','2022-11-11 11:11:11') d5,
	DATEDIFF('2022-12-12 23:59:59','2022-11-11 11:11:11') d6,
	
	DATEDIFF('2022-12-12 00:00:00','2022-11-11 23:59:59') d7,
	DATEDIFF('2022-12-12 12:12:12','2022-11-11 23:59:59') d8,
	DATEDIFF('2022-12-12 23:59:59','2022-11-11 23:59:59') d9

在这里插入图片描述

TIMESTAMPDIFF函数

可用于计算两日期时间之间相差的天数，秒数，分钟数，周数，小时数
语法：TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
unit单位有如下几种：FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

-- 例如：
# 计算两日期之间相差多少秒
select timestampdiff(SECOND,'1997-10-12','2022-01-10');
# 计算两日期之间相差多少天
select timestampdiff(DAY,'1997-10-12','2022-01-10');
# 计算工龄
SELECT TIMESTAMPDIFF(YEAR,'2019-07-20',NOW()) AS 'working years';