Bug #58513 TIMEDIFF comparison results appear wrong
Submitted: 26 Nov 2010 10:19 Modified: 22 Dec 2010 22:12
Reporter: Daniel Fiske Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.52, 5.0, 5.1 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: timediff

[26 Nov 2010 10:19] Daniel Fiske
Description:
TIMEDIFF with > or < appear wrong. I'm not sure if this is correct behaviour, but it seems wrong.

How to repeat:
Test 1
======

SELECT TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00"),TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00") < TIME("-1:25:00.0")

TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00")  TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00") < TIME("-1:25:00.0")
-----------------------------------------------------  --------------------------------------------------------------------------
-01:00:00                                                                                                                       1

Surely -01:00:00 !< -1:25:00.0? as I remember it from the number line.

Test 2
======

SELECT TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00"),TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00") BETWEEN TIME("-1:25:00.0") AND TIME("-00:25:00.0");

TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00")  TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00") BETWEEN TIME("-1:25:00.0") AND TIME("-00:25:00.0")
-----------------------------------------------------  --------------------------------------------------------------------------------------------------------
-01:00:00                                                                                                                                                     0

Surely -1:00:00 sits nicely between -1:25:00 and -0:25:00?
[26 Nov 2010 10:24] Daniel Fiske
It should be noted that using TIME_TO_SEC returns what I would describe as the correct results

SELECT TIME_TO_SEC(TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00")),TIME_TO_SEC(TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00")) 
BETWEEN TIME_TO_SEC(TIME("-1:25:00.0")) AND TIME_TO_SEC(TIME("-00:25:00.0"));

time_to_sec(TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00"))  TIME_TO_SEC(TIMEDIFF("2010-01-01 05:00:00","2010-01-01 06:00:00")) BETWEEN TIME_TO_SEC(TIME("-1:25:00.0")) AND TIME_TO_SEC(TIME("-00:25:00.0"))
------------------------------------------------------------------  -----------------------------------------------------------------------------------------------------------------------------------------------
                                                             -3600                                                                                                                                                1
[27 Nov 2010 12:03] Sveta Smirnova
Thank you for the report.

Verified as described in version 5.0 and 5.1. Does not exist in version 5.5.7. Can be side effect of not full fix of bug #42525 although cast to TIME does not help.
[22 Dec 2010 22:13] Omer Barnir
Issue fixed in 5.5 and will not be back ported