mysqltimestamp

How to create a condition for inserted less than a day ago?

I have fields in the database (userid, code, timestamp)

I want to verify in this code if a userid and the respective code exists but that was inserted less than one day ago, otherwise show a message "link expired"

My code works well without this line TIMESTAMPDIFF(DAY, CURTIME(), timestamp) < 1").

At the moment nothing is returned with the timestamp condition

select userid, code from password_reset  where userid=? and code=? and TIMESTAMPDIFF(DAY, CURTIME(), timestamp) < 1

Is there a problem with this code? Basically, my idea is: if the timestamp has more than a day, return an error to the user.

Solution

  • You want to pass a DATETIME value, not just a TIME, use NOW() not CURTIME(). (If you want to ignore the time of day, then use CURDATE().

    Also, reverse the argument order, as the function does datetime2 - datetime1.

    See the documentation for more details.

    TIMESTAMPDIFF(DAY, `timestamp`, NOW()) < 1

    Using a reserved word for your column name won't help either.