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