mysql服务器版本:5.0.95 datediff timemediff不工作

Question

最新版本的mysql服务器5.7和我使用的5.0。在情况下mysql服务器版本:5.0.95 datediff timemediff不工作，欢迎任何帮助建议。可能是进近时的小错误。

mysql> describe agent_idel_time;
+-----------------+------------------+------+-----+---------------------+-------+
| Field           | Type             | Null | Key | Default             | Extra |
+-----------------+------------------+------+-----+---------------------+-------+
| id              | int(10) unsigned | NO   | PRI | 0                   |       |
| holdtime        | datetime         | YES  |     | NULL                |       |
| hold_max        | int(11)          | NO   |     | NULL                |       |
| hold_min        | int(11)          | NO   |     | NULL                |       |
| hold_avg        | int(11)          | NO   |     | NULL                |       |
| hold_start_time | datetime         | NO   |     | 0000-00-00 00:00:00 |       |
| hold_end_time   | datetime         | NO   |     | 0000-00-00 00:00:00 |       |
| htime           | varchar(255)     | NO   |     | NULL                |       |
+-----------------+------------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> select * from agent_idel_time G;
*************************** 1. row ***************************
             id: 1
       holdtime: 0000-00-00 00:00:00
       hold_max: 55
       hold_min: 5
       hold_avg: 30
hold_start_time: 2016-06-30 15:10:00
  hold_end_time: 2016-06-30 15:12:00
          htime: 00:00:00
*************************** 2. row ***************************
             id: 2
       holdtime: 0000-00-00 00:00:00
       hold_max: 57
       hold_min: 7
       hold_avg: 32
hold_start_time: 2016-06-30 17:10:00
  hold_end_time: 2016-06-30 17:12:00
          htime: 00:00:00
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql>
mysql> UPDATE agent_idel_time SET htime = time_format(TIMEDIFF( `hold_end_time` , `hold_start_time` ) ,'%H:%m:%s');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0
mysql>
mysql> select * from agent_idel_time G; 
*************************** 1. row ***************************
             id: 1
       holdtime: 0000-00-00 00:00:00
       hold_max: 55
       hold_min: 5
       hold_avg: 30
hold_start_time: 2016-06-30 15:10:00
  hold_end_time: 2016-06-30 15:12:00
          htime: 00:00:00
*************************** 2. row ***************************
             id: 2
       holdtime: 0000-00-00 00:00:00
       hold_max: 57
       hold_min: 7
       hold_avg: 32
hold_start_time: 2016-06-30 17:10:00
  hold_end_time: 2016-06-30 17:12:00
          htime: 00:00:00
2 rows in set (0.00 sec)
ERROR:
No query specified

Answer 1

您为time_format函数设置了错误的格式字符串。

分钟，数字(00..59)是 %i ，而不是 %m (这是月，数字(00..12))，所以更改您的更新语句为:

UPDATE agent_idel_time 
SET htime = time_format(TIMEDIFF(`hold_end_time`, `hold_start_time`) ,'%H:%i:%s');

或者您可以使用 '%T' ，它是时间，24小时(hh:mm:ss)代替。

格式代码参考