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_01(t1 time,t2 time,t3 time);
2. insert into time_01 values("-838:59:59.0000","838:59:59.00","22:00:00");
3. insert into time_01 values("0:00:00.0000","0","0:00");
4. insert into time_01 values(null,NULL,null);
5. insert into time_01 values("23","1122","-1122");
6. insert into time_01 values("101412","4","-101219");
7. insert into time_01 values("24:59:09.932823","24:02:00.93282332424","24:20:34.00000000");
8. insert into time_01 values("2022-09-08 12:00:01","019","23403");

10. mysql> select * from time_01;
11. +------------+-----------+-----------+
12. | t1         | t2        | t3        |
13. +------------+-----------+-----------+
14. | -838:59:59 | 838:59:59 | 22:00:00  |
15. | 00:00:00   | 00:00:00  | 00:00:00  |
16. | NULL       | NULL      | NULL      |
17. | 00:00:23   | 00:11:22  | -00:11:22 |
18. | 10:14:12   | 00:00:04  | -10:12:19 |
19. | 24:59:10   | 24:02:01  | 24:20:34  |
20. | 12:00:01   | 00:00:19  | 02:34:03  |
21. +------------+-----------+-----------+
22. 7 rows in set (0.00 sec)

24. mysql> select timediff(t1,t2) from time_01;
25. +------------------+
26. | timediff(t1, t2) |
27. +------------------+
28. | -1677:59:58      |
29. | 00:00:00         |
30. | NULL             |
31. | -00:10:59        |
32. | 10:14:08         |
33. | 00:57:09         |
34. | 11:59:42         |
35. +------------------+
36. 7 rows in set (0.00 sec)