timediff的意外结果

本文介绍了timediff的意外结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

timediff函数不能按预期方式工作.在下面的示例中，我尝试计算1年内的秒数差异.

The timediff function does not work as expected. In the following example I am trying to calculate the difference in seconds for a period of 1 year.

mysql>SELECT 366*24*60*60 AS expected; +----------+ | expected | +----------+ | 31622400 | +----------+ 1 row in set (0.00 sec) mysql>SELECT ABS(UNIX_TIMESTAMP('2000:01:01 00:00:00') - UNIX_TIMESTAMP('2001:01:01 00:00:00')); +------------------------------------------------------------------------------------+ | ABS(UNIX_TIMESTAMP('2000:01:01 00:00:00') - UNIX_TIMESTAMP('2001:01:01 00:00:00')) | +------------------------------------------------------------------------------------+ | 31622400 | +------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>SELECT TIME_TO_SEC(TIMEDIFF('2000:01:01 00:00:00', '2001:01:01 00:00:00')); +---------------------------------------------------------------------+ | TIME_TO_SEC(TIMEDIFF('2000:01:01 00:00:00', '2001:01:01 00:00:00')) | +---------------------------------------------------------------------+ | -3020399 | +---------------------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) Warning (Code 1292): Truncated incorrect time value: '-8784:00:00'

推荐答案

编辑:您正在使用哪个版本的MySQL?至少在5.0.22上可以正常工作.我只是跑了这个查询.看到这里

What version of MySQL are you using? It works fine on 5.0.22 at least. I just ran this query. see here

mysql> SELECT TIME_TO_SEC(TIMEDIFF('2000:01:01 00:00:00', '2001:01:01 00:00:00')); +---------------------------------------------------------------------+ | TIME_TO_SEC(TIMEDIFF('2000:01:01 00:00:00', '2001:01:01 00:00:00')) | +---------------------------------------------------------------------+ | -31622400 | +---------------------------------------------------------------------+ 1 row in set (0.00 sec)

您看到此处了吗? 可能是截断问题，因为时间范围比您的日期差小得多.

Did you see here? It may be truncation issue because Time range is much smaller than the date difference that you have.

原始答案

使用此

SELECT TIMESTAMPDIFF(SECOND,'2000:01:01 00:00:00', '2001:01:01 00:00:00'); +--------------------------------------------------------------------+ | TIMESTAMPDIFF(SECOND,'2000:01:01 00:00:00', '2001:01:01 00:00:00') | +--------------------------------------------------------------------+ | 31622400 | +--------------------------------------------------------------------+

问题是您正在尝试将负时间转换为秒.交换变量即可完成工作.

The problem is you're trying to convert a negative time to time-in-second. Swapping the variables will do the job.

SELECT TIME_TO_SEC(TIMEDIFF('2001:01:01 00:00:00','2000:01:01 00:00:00')); +--------------------------------------------------------------------+ | TIME_TO_SEC(TIMEDIFF('2001:01:01 00:00:00','2000:01:01 00:00:00')) | +--------------------------------------------------------------------+ | 31622400 | +--------------------------------------------------------------------+