美文网首页
MySQL中TIMESTAMPDIFF和DATEDIFF函数的区

MySQL中TIMESTAMPDIFF和DATEDIFF函数的区

作者: 对三zzzzzzz | 来源:发表于2020-02-25 08:01 被阅读0次

    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

    相关文章

      网友评论

          本文标题:MySQL中TIMESTAMPDIFF和DATEDIFF函数的区

          本文链接:https://www.haomeiwen.com/subject/sffzqhtx.html