JZTXT

MYSQL时间差函数

发布时间 2023-09-28 15:08:10作者: DL东和易

一、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