Bug #12428 | Working with TIMEs problem | ||
---|---|---|---|
Submitted: | 8 Aug 2005 8:40 | Modified: | 11 Aug 2005 16:31 |
Reporter: | Ante Drnasin | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 4 ... 5.0 | OS: | Any (*) |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[8 Aug 2005 8:40]
Ante Drnasin
[8 Aug 2005 10:23]
Aleksey Kishkin
mysql> SELECT IF(TIME('101:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE'); +-------------------------------------------------------------+ | IF(TIME('101:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE') | +-------------------------------------------------------------+ | TRUE | +-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT IF(TIME('99:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE'); +------------------------------------------------------------+ | IF(TIME('99:00:00') <= TIME('102:00:00'), 'TRUE', 'FALSE') | +------------------------------------------------------------+ | FALSE | +------------------------------------------------------------+ 1 row in set (0.00 sec)
[8 Aug 2005 10:27]
Ante Drnasin
Hi Alexey and thanx for the reply but as you see I'm working with ADDTIME...did you try MY query that I have sent?I know that this you gave work but this query always returns false which is weird.... SELECT IF(ADDTIME('88:00:00', '08:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter Try running that query thru your DB and then let me know the result... for now I'm using SELECT IF(TIME_TO_SEC(ADDTIME('88:00:00', '08:00:00')) <= TIME_TO_SEC('168:00:00'), 'TRUE', 'FALSE') AS canEnter whihc is very very bad workaround.... I still think there's a BUG somewhere but I just can't put my finger on it....
[8 Aug 2005 10:36]
Ante Drnasin
Hi again :) I tried wrapping the results in TIME 1. SELECT IF(TIME(ADDTIME('88:00:00', '08:00:00')) <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter canEnter = false //WRONG!! 2. 88:00:00 + 08:00:00 = 96:00:00 so... SELECT IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') AS canEnter canEnter = false //WRONG!! So you see there is something wrong....how can they both be false...it's just not right....or am I missing something here?
[8 Aug 2005 11:12]
Ante Drnasin
again..but this time from command prompt... mysql> SELECT IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE'); +------------------------------------------------------------+ | IF(TIME('96:00:00') <= TIME('168:00:00'), 'TRUE', 'FALSE') | +------------------------------------------------------------+ | FALSE | +------------------------------------------------------------+ 1 row in set (0.00 sec)
[8 Aug 2005 13:40]
Andrey Hristov
TIME() returns a string because it is used in such a context. And "96:00:00" is greater than "168:00:00" when using string comparison. Using TIME_TO_SEC is the right way to perform the comparison. Comparing dates and datetimes works the same way - string comparison but there there is no value over 99, which in this case creates the "problem".
[8 Aug 2005 13:44]
Ante Drnasin
Thank you for the explanation Andrey but from the programers point of view this is just IMHO very very stupid behaviour...I think that you should document that ANY operation on TIME values should first be converted to seconds...
[11 Aug 2005 16:31]
Mike Hillyer
Documented that the return type of the TIME() function is a string.