I can now select and add the time but when comparing with times in the DB it is not working as I expected, i am trying to select only records where the time in the database + 1 hour is superior at the current time but all records are returned:
SELECT codeId, codetime, ADDDATE(codetime, INTERVAL 1 HOUR) as limittime FROM tbllogin_codes WHERE ADDDATE(codetime, INTERVAL 1 HOUR) > curtime();
i have changed from time to date but it is still not working as expected, it still returns very records that have the smae date as the current date regardless of the time, should I change the DB and use a time field in stead?
The problem comes from your "codetime" field being a DATE, not a TIME... We are just turning MySQL mad with DATETIME / TIME functions or convertions ! TIME_TO_SEC should only be used with TIME, and ADDTIME(datetime) cannot be correctly compared to a time. And unfortunately, when we tried CURDATE(), it was returning a date without time portion. Use Now(), which returns the current DATE AND TIME :
SELECT codeId, codetime, ADDTIME(codetime, '02:00:00.000000') as limittime
WHERE ADDTIME(codetime, '02:00:00.000000') > Now();
more bad news, I am getting a error 1064, check the manual.............
It seems that every time I try to use ADDTIME() i get an error, thus I use one of the latest version.
I have try to change the field to a time instead of a date / time but I get the same error, have also try different suggestions from above without any result!
Are you sure ? Are you sure that, in the rows you posted before, the last one (provided the date part is not here anymore) is still returned ? Other ones should be, but not the last one. Of course, since it is about 17:00, if all your rows are like time + 2 hours = 20:00, they will all be returned.