Bug #57309 TIMEDIFF function giving same value for different date
Submitted: 7 Oct 2010 11:35 Modified: 7 Oct 2010 13:35
Reporter: ramesh npr Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: TIMEDIFF function giving same value

[7 Oct 2010 11:35] ramesh npr
Description:
I am doing select for TIMEDIFF for different date (> than 11th Nov 2010), for all select query its giving same result. Please give solution to fix this issue. Its urgent

How to repeat:

mysql> select TIMEDIFF('2010-11-11 20:40:57',now());
+---------------------------------------+
| TIMEDIFF('2010-11-11 20:40:57',now()) |
+---------------------------------------+
| 838:59:59                             |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select TIMEDIFF('2010-11-13 20:40:57',now());
+---------------------------------------+
| TIMEDIFF('2010-11-13 20:40:57',now()) |
+---------------------------------------+
| 838:59:59                             |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>
[7 Oct 2010 12:05] Peter Laursen
-- try this

SELECT TIMEDIFF('2010-10-11 20:40:57',NOW()); -- returns 102:42:47 at this very momeent

-- so result seems restricted to less than 839 hours (just a little more than a month)
-- (839*60*60 - 1 = 3020399; weird number IMHO)
[7 Oct 2010 13:10] Peter Laursen
workaround could be to convert to unix_timestamp, CAST to integer (if necessary - I do not know) and subtract the values. This will return the difference in seconds as a plain number. I think this is a very common way to do in MySQL.

Next format as you need/want using simple arithmetics and string functions if you need formatting. Note that SEC_TO_TIME() has exactly same issue as TIMEDIFF() in this respect (try SELECT SEC_TO_TIME(11111111111111111111111111111111)).

Peter
(not a MySQL person)
[7 Oct 2010 13:35] Valeriy Kravchuk
This is NOT a bug actually. TIMEDIFF() returns TIME data type:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot --column-type-in
fo -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.51-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select timediff(now(), now()) as t;
Field   1:  `t`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       TIME
Collation:  binary (63)
Length:     23
Max_length: 8
Decimals:   6
Flags:      BINARY

+----------+
| t        |
+----------+
| 00:00:00 |
+----------+
1 row in set (0.01 sec)

and our manual clearly explains range of possible values for TIME data type. Read http://dev.mysql.com/doc/refman/5.1/en/time.html.
[7 Oct 2010 13:38] Valeriy Kravchuk
You even get a warning:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.51-community MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select TIMEDIFF('2010-11-13 20:40:57',now());
+---------------------------------------+
| TIMEDIFF('2010-11-13 20:40:57',now()) |
+---------------------------------------+
| 838:59:59                             |
+---------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect time value: '892:04:40'
1 row in set (0.00 sec)

In some cases DATEDIFF() can be also used as a workaround (in addition to Peter's ideas above).

mysql> select DATEDIFF('2010-11-13 20:40:57',now());
+---------------------------------------+
| DATEDIFF('2010-11-13 20:40:57',now()) |
+---------------------------------------+
|                                    37 |
+---------------------------------------+
1 row in set (0.02 sec)