Unexpected results for timediff

Question

The timediff function is not working properly. In the following example, I am trying to calculate the difference in seconds over 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' 

Answer 1

Edit:. What version of MySQL are you using? It works fine on 5.0.22. I just fulfilled this request. 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) 

Do you see here ? This could be a truncation problem, since the time range is much smaller than the date difference you have.

---

Original answer

Use this

  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 that you are trying to convert a negative time into a stopwatch. The permutation of the variables will complete the task.

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