| 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: | |
| 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: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
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?