MYSQL时间差函数

一、TIMESTAMPDIFF

1. 语法

   TIMESTAMPDIFF(unit, datetime1, datetime2)
   返回datetime2 - datetime1的时间差, 结果单位由unit参数决定
   
   unit合法参数
     second 秒
     minute 分
     hour 小时
     day 天
     week 周
     month 月
     quarter 季度
     year 年
   

2. 示例

   SELECT TIMESTAMPDIFF(SECOND, "2023-09-27 12:00:00", "2023-09-28 12:00:00") diff_time;  # 86400
   
   SELECT TIMESTAMPDIFF(MINUTE, "2023-09-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 1440
   
   SELECT TIMESTAMPDIFF(HOUR, "2023-09-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 24
   
   SELECT TIMESTAMPDIFF(DAY, "2023-09-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 1
   SELECT TIMESTAMPDIFF(DAY, "2023-09-27 12:00:00", "2023-09-28 10:00:00") diff_time; # 0
   
   SELECT TIMESTAMPDIFF(WEEK, "2023-08-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 4
   
   SELECT TIMESTAMPDIFF(MONTH, "2023-08-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 1
   
   SELECT TIMESTAMPDIFF(QUARTER, "2023-02-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 2
   
   SELECT TIMESTAMPDIFF(YEAR, "2022-02-27 12:00:00", "2023-09-28 12:00:00") diff_time; # 1
   

二、DATEDIFF

1. 语法

   DATEDIFF(date1, date2)
   返回date1 - date2的天数值
   

2. 示例

   SELECT DATEDIFF("2023-09-28", "2023-09-27") diff_time; # 1
   
   SELECT DATEDIFF("2023-09-02", "2023-08-30") diff_time; # 3
   
   SELECT DATEDIFF("2023-09-02", "2022-09-02") diff_time; # 365
   

三、TIMEDIFF

1. 语法

   TIMEDIFF(datetime1, datetime2)
   返回datetime1 - datetime2的时间差
   

2. 示例

   SELECT TIMEDIFF("2023-09-28 12:00:00", "2023-09-27 10:00:00") diff_time; # 26:00:00
   
   SELECT TIMEDIFF("2023-09-28 12:00:00", "2023-09-28 10:00:00") diff_time; # 02:00:00