Mysql get every interval

Question

0.00/5 (No votes)

See more:

I had wrote the below query because I wanted to return the list of every user that joined when the interval was 9 days using curdate() and joineddate. For example if today was 6/30

username joineddate
user1 | 6/21
user2 | 6/20
user3 | 6/12
user4 | 6/03
user5 | 6/01

But I realized that this only returns 1 date (user1)

How can this return users (user1, user3, user4) since they met the 1 week criteria

SQL

SELECT * FROM users where (DATEDIFF(CURDATE(),joineddate) = 9)

What I have tried:

I have tried using the interval function and datediff

Posted 8-Jul-24 6:14am

mcbain19

Updated 8-Jul-24 6:44am

Dave Kreskowiak

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2024-07-08T06:43:00

Solution 1

The problem is you're subtracting the joined date from the current date and seeing if it is EQUAL to 9. That will only ever match a single date. In your example, that would be everyone who joined only on 6/21, or (6/30 - 6/21 = 9).

If you're looking for everyone who joined in the last 9 days, you have to use a different comparison operator, less than or equal to, or <=.

SQL

SELECT * FROM users where (DATEDIFF(CURDATE(),joineddate) <= 9)

Posted 8-Jul-24 6:43am

Dave Kreskowiak

Comments

mcbain19 8-Jul-24 12:58pm

Thanks Dave, but I am looking for an every 9 day interval so return user if joineddate condition matches (9 days, 18 days, 27 days, 36 days... starting from joineddate)

Dave Kreskowiak 8-Jul-24 13:17pm

That isn't very clear. Are you saying you want the users who joined exactly 9 days ago, or between now and 9 days ago, or 9 to 18 days ago, and so on?

Dave Kreskowiak 8-Jul-24 14:05pm

OK, rereading this, I think I understand now. Wendelius has the answer that would work.

Wendelius · Answer 2 · 2024-07-08T08:00:00

Solution 2

Perhaps something like

SQL

SELECT * FROM users WHERE MOD(DATEDIFF(CURDATE(), joineddate), 9) = 0

Posted 8-Jul-24 8:00am

Wendelius

Comments

mcbain19 10-Jul-24 8:08am

Yes this works perfectly Wendelius thanks for your help.