MySQL - Comparing Two Negative Time Values

Question

I have the following conditional statement in my SQL query:

WHERE TIMEDIFF("03:00:00", VARIABLE) >= "00:05:00"

The above works as expected. If the value returned from TIMEDIFF() is greater than 5 minutes, the record it represents will be returned. If TIMEDIFF() returns -02:00:00, -00:05:00, or 00:04:00, then the record would be excluded. If it returns 00:06:00 or 01:02:00, the corresponding record will be included in the query results.

I need to update the above conditional statement to:

WHERE TIMEDIFF("03:00:00", VARIABLE) >= "-00:05:00"

In the updated statement, only values greater than or equal to -00:05:00 should be included in the results. I noticed that this isn't the case. Even records with corresponding larger negative values returned by TIMEDIFF() - ie. -05:00:00, -02:00:00, -00:06:00 - are being returned with this new conditional statement.

It seems that MySQL has trouble comparing two negative time values against one another.

Is there any way to accurately write this comparison?

score 3 · Accepted Answer · answered Jan 15 '19 at 07:38

3

use TIME_TO_SEC to convert time format to integer second then compare your values

like below

where TIME_TO_SEC(TIMEDIFF("03:00:00", VARIABLE))>=-300

answered Jan 15 '19 at 07:38

Zaynul Abadin Tuhin

31,407
5
33
63

Maksym Fedorov · Answer 2 · 2019-01-15T08:31:04.590

Mysql can work with time in the range from -838:59:59 to 838:59:59.

First, you should make sure TIMEDIFF() returns correct time. For example, TIMEDIFF() can return an error with using jdbc driver in cases with negative time. You can check it with the simple query:

SELECT TIMEDIFF("02:55:00", "03:00:00")

if result is '-05:00:00' then a driver works correctly.

Secondly, you should cast your time value in time type manually, because MySQL can cast a negative time value to time type incorrectly.

WHERE TIMEDIFF("03:00:00", VARIABLE) >= TIME("-00:05:00")

For example:

SELECT TIMEDIFF("03:00:00", "3:00:00") >= "-00:05:00"; // return true
SELECT TIMEDIFF("03:00:00", "3:10:00") >= "-00:05:00"; // return true

This query always returns true, but if you cast '-00:05:00' to time type manually this queries will return the correct results

SELECT TIMEDIFF("03:00:00", "3:00:00") >= TIME("-00:05:00"); // return true
SELECT TIMEDIFF("03:00:00", "3:10:00") >= TIME("-00:05:00"); // return false

score 0 · Answer 3 · edited Jan 15 '19 at 08:51

Hm, you could use TIMESTAMPDIFF (documentation), in order to do that, you need convert your hard coded value twice (if you cast it directly to datetime, it TIMESTAMPDIFF won't work properly), like this:

create table tbl (t time);
insert into tbl values ("00:06:00"), ("00:03:00"), ("-00:06:00"), ("-00:03:00");
-- here you can see how it works
select *,
       TIMESTAMPDIFF(SECOND, cast(cast("00:05:00" as time) as datetime), cast(t as datetime))
from tbl;

Mentioned function will return an integer, which will be easy to compare against :)

MySQL - Comparing Two Negative Time Values

3 Answers3