1、TIMESTAMPDIFF:后面的时间减去前面的时间
SELECT
-- 时间相差不到24小时不算一天
TIMESTAMPDIFF(DAY,'2020-02-25 00:00:00','2020-02-26') day1, -- 1
TIMESTAMPDIFF(DAY,'2020-02-25 00:00:01','2020-02-26') day2, -- 0
TIMESTAMPDIFF(DAY,'2020-02-25 11:00:11','2020-02-26 11:00:11') day3, -- 1
TIMESTAMPDIFF(DAY,'2020-02-25 11:00:11','2020-02-26 14:00:11') day4, -- 1
TIMESTAMPDIFF(DAY,'2020-02-25 11:00:11','2020-02-26 10:00:11') day5 -- 0
image.png
总结:无论时间单位是什么,不足则不取
2、DATEDIFF:前面的时间减去后面的时间
select
-- 只取前面日期部分进行计算
DATEDIFF('2020-02-26','2020-02-25 00:00:00') day1, -- 1
DATEDIFF('2020-02-26','2020-02-25 00:00:01') day2, -- 1
DATEDIFF('2020-02-26 11:00:11','2020-02-25 11:00:11') day3, -- 1
DATEDIFF('2020-02-26 14:00:11','2020-02-25 11:00:11') day4, -- 1
DATEDIFF('2020-02-26 10:00:11','2020-02-25 11:00:11') day5 -- 1
image.png
网友评论