TIMEDIFF()

函数说明

TIMEDIFF() 返回两个 TIME 或 DATETIME 值之间的差值。TIMEDIFF() 函数的两个表达式必须为相同类型的参数，即 TIME 或 DATETIME。TIMEDIFF 函数返回表示为时间值的 expr1 - expr2 的结果。

函数语法

  1. > TIMEDIFF(expr1,expr2)

参数释义

参数说明
expr1, expr2必要参数。expr1 和 expr2 表达式需要具有相同的类型。expr1 和 expr2 是转换为 TIME 或 DATETIME 表达式的字符串。如果 expr1 或 expr2 为 NULL 则返回 NULL。

示例

  1. mysql> select timediff("22:22:22", "11:00:00");
  2. +------------------------------+
  3. | timediff(22:22:22, 11:00:00) |
  4. +------------------------------+
  5. | 11:22:22.000000              |
  6. +------------------------------+
  7. 1 row in set (0.01 sec)
  9. mysql> select timediff(cast('22:22:22' as time), null);
  10. +--------------------------------------------+
  11. | timediff(cast(22:22:22 as time(26)), null) |
  12. +--------------------------------------------+
  13. | NULL                                       |
  14. +--------------------------------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> select timediff(CAST('2017-08-08 22:22:22' as datetime), CAST('2000-01-02 11:00:00' as datetime));
  18. +------------------------------------------------------------------------------------------------+
  19. | timediff(cast(2017-08-08 22:22:22 as datetime(26)), cast(2000-01-02 11:00:00 as datetime(26))) |
  20. +------------------------------------------------------------------------------------------------+
  21. | 154283:22:22                                                                                   |
  22. +------------------------------------------------------------------------------------------------+
  23. 1 row in set (0.00 sec)
  1. create table time_04 (t1 int,t2 time,t3 datetime,t4 timestamp);
  2. insert into time_04 values (1,"344:59:09","2020-09-12","2021-09-22 10:01:23.903");
  4. mysql> select * from time_04;
  5. +------+-----------+---------------------+---------------------+
  6. | t1   | t2        | t3                  | t4                  |
  7. +------+-----------+---------------------+---------------------+
  8. |    1 | 344:59:09 | 2020-09-12 00:00:00 | 2021-09-22 10:01:24 |
  9. +------+-----------+---------------------+---------------------+
  10. 1 row in set (0.00 sec)