CODEWITHSUNDEEP
mysql
David
David

Reputation: 4007

Mysql Date manipulation and divisible by

I have a cron that runs some php with some mysql just after midnight everyday. I want to take all registered users (to my website) and send them a reminder and copy of the newsletter. However I want to do this every 30 days from their registration.

I have thought as far as this:

SELECT * FROM users
WHERE DATE(DT_stamp) = DATE(NOW() - INTERVAL 30 DAY

But this will only work for 30 days after they have registered, not 60 and 90.

Effectively I want:

Where days since registration is divisible by 30

That way every 30 days that user will get picked up in the sql.

Can someone help me formulate this WHERE clause, I am struggling with mysql where day(date1-date2) divisible 30

Upvotes: 6

Views: 1881

Answers (4)

6opko
6opko

Reputation: 1760

Just an addition (not that nine years had passed :)

If you want to skip today's date you should add

AND DATEDIFF(NOW(), DT_stamp) != 0;

making it

SELECT * FROM users WHERE MOD(DATEDIFF(NOW(), DT_stamp), 30) = 0 AND DATEDIFF(NOW(), DT_stamp) != 0;

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

or the other way round...

SELECT * FROM users WHERE MOD(DATEDIFF(NOW(),registration_date),30) = 0;

Upvotes: 3

Bohemian
Bohemian

Reputation: 425083

Use SQL modulo function MOD():

SELECT * FROM users
WHERE MOD( DATE(DT_stamp) - DATE(NOW()), 30) = 0

In mysql, you can also use the % operator, which does the same thing:

SELECT * FROM users
WHERE (DATE(DT_stamp) - DATE(NOW()) % 30 = 0

Upvotes: 2

Joni
Joni

Reputation: 111299

The DATEDIFF function returns the difference between two dates in days, ignoring the time:

SELECT * FROM users
WHERE DATEDIFF(DT_stamp, NOW()) % 30 = 0

Upvotes: 5

Related Questions