mysql query with date hours and interval

Question

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?

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, 2022 at 11:17
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, 2022 at 22:33

Brugere · Accepted Answer · 2022-01-17 09:22:39Z

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

answered Jan 14, 2022 at 10:39

Brugere

1034 bronze badges

1

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

Add a comment |

1 Answer 1