mysql - timediff 的意外结果

Question

timediff 函数没有按预期工作。在以下示例中，我尝试计算 1 年期间的秒数差异。

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'

Answer 1

编辑:您使用的是哪个版本的 MySQL？它至少在 5.0.22 上运行良好。我刚刚运行了这个查询。看这里

 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)

你有没有see here ？ 可能是截断问题，因为时间范围比您的日期差异小得多。

---

原始答案

使用这个

    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 |
    +--------------------------------------------------------------------+

问题是您正试图将负时间转换为秒时间。交换变量即可。

    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 |
    +--------------------------------------------------------------------+