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
    Commented Jan 14, 2022 at 11:17
  • can u please provide a exampel CMd for it?
    – iBBi
    Commented Jan 14, 2022 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
    Commented Jan 14, 2022 at 22:33

2 Answers 2

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);

3
  • 1
    Do not even breathe "curdate() + 1" -- It computes incorrectly!
    – Rick James
    Commented Jan 14, 2022 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
    Commented Jan 17, 2022 at 9:22
  • 1
    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
    Commented Jan 17, 2022 at 17:59
0

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

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

If today is '2023-12-21' this will fetch users with expiration as '2023-12-22 18:30:10' and '2023-12-22 00:00:00'.

Assuming expirations are generally based on date only (not including the time component).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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