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: | |
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
[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)