0

I have EXPIRATION column in mysql table which contains date in following format

2022-02-15 08:00:00

I am trying to query username whose expiry is on 2022-02-17 08:00:00 using below query

SELECT username FROM users WHERE expiration = DATE_ADD(curdate(), INTERVAL 1 DAY);

but getting no result. is something wrong with my query?

3
  • 1
    CURDATE() returns date component only, with zero timepart. So your query searches only the rows which' expiration timepoint is the midnight (00:00:00) strictly. Either cut off the timepart from expiration column value or use unequiation.
    – Akina
    Jan 14 at 11:17
  • can u please provide a exampel CMd for it?
    – iBBi
    Jan 14 at 16:05
  • Smells like you are in Pacific time and mixing DATETIMEs and TIMESTAMPs? Let's talk about cleaning up the data before talking about date arithmetic.
    – Rick James
    Jan 14 at 22:33
0

Your request will return the username of your users tables which have an expiration strictly equal to the DATE_ADD(curdate(), INTERVAL 1 DAY)

Changing = for < should help getting more results.

SELECT username FROM users WHERE expiration < DATE_ADD(curdate(), INTERVAL 1 DAY);

New contributor
Brugere is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • 1
    Do not even breathe "curdate() + 1" -- It computes incorrectly!
    – Rick James
    Jan 14 at 22:32
  • Can't tell if you're taking this computation literally, but this was an illustration. Maybe the code block was misleading, sorry for that. I thought my last block (with full query was clear enough), thank you for highlighting this.
    – Brugere
    yesterday
  • Thanks for fixing it. "+1" works some of the time and it could lead users into bugs that are hard to figure out.
    – Rick James
    yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.