MySQL中TIMESTAMPDIFF和DATEDIFF函数的区别

最新推荐文章于 2025-09-02 16:04:15 发布

原创最新推荐文章于 2025-09-02 16:04:15 发布 · 1.8k 阅读

4 ·

CC 4.0 BY-SA版权

sql 专栏收录该内容

5 篇文章

订阅专栏

博客介绍了两个时间计算函数的差异，TIMESTAMPDIFF是用后面的时间减去前面的时间，而DATEDIFF则是用前面的时间减去后面的时间。

TIMESTAMPDIFF：后面的时间减去前面的时间

select 
-- 时间相差不到24小时不算一天  
 TIMESTAMPDIFF(DAY,'2017-06-02 00:00:00','2017-06-03') day1, -- 1
 TIMESTAMPDIFF(DAY,'2017-06-02 00:00:01','2017-06-03') day2,  -- 0
 TIMESTAMPDIFF(DAY,'2017-06-02  11:00:11','2017-06-03 11:00:11') day3, -- 1
 TIMESTAMPDIFF(DAY,'2017-06-02  11:00:11','2017-06-03 14:00:11') day4, -- 1
 TIMESTAMPDIFF(DAY,'2017-06-02  11:00:11','2017-06-03 10:00:11') day5 -- 0

运行结果

DATEDIFF：前面的时间减去后面的时间

select 
-- 只取前面日期部分进行计算
 DATEDIFF('2017-06-03','2017-06-02 00:00:00') day1, -- 1
 DATEDIFF('2017-06-03','2017-06-02 00:00:01') day2,  -- 1
 DATEDIFF('2017-06-03 11:00:11','2017-06-02  11:00:11') day3, -- 1
 DATEDIFF('2017-06-03 14:00:11','2017-06-02  11:00:11') day4, -- 1
 DATEDIFF('2017-06-03 10:00:11','2017-06-02  11:00:11') day5 -- 1

在这里插入图片描述