日常开发中使用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';