timediff的意外结果 [英] unexpected results for 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 |
    +--------------------------------------------------------------------+

这篇关于timediff的意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！