CODEWITHSUNDEEP
mysqldatetime
Kerry Jones
Kerry Jones

Reputation: 21838

MySQL - Find the same day every month

I have created a system that will be calculating data every month based on when a user has signed up.

I will be running a daily cron job to grab all users who need there information calculated, and I would like to grab all the users for each day.

If users all signed up on something like 2011-01-01

A simply query would find that (if yesterday was the end of the period):

SELECT `user_id` 
FROM `table` 
WHERE DAY( `date_created` ) = DAY( DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY ) )

It gets more complicated if they signed up on 2011-03-31

What query can I use to make sure that on 2011-04-30 they would be found?

My guess is something like

SELECT `user_id`
FROM `table`
WHERE 
    DAY( `date_created` ) = DAY( DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY ) )
    OR DAY( DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY ) ) < DAY( `date_created` )
        AND LAST_DAY( DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY ) ) = DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY )

That does not seem very efficient, nor do I know if it would work (though I suspect it does).

Is there a tried an true method? Or any better way to do this?

Upvotes: 1

Views: 1166

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

All you need to do is check if it's the first of the month, if it is do a >= comparison, otherwise do a = comparison.

WHERE IF(DAY(CURRENT_DATE()) = 1,
    DAY( `date_created` ) >= DAY(DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY )),
    DAY( `date_created` ) = DAY(DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY ))
)

So on the 1st of March, it will update users who registered on or after the 28th (in a non leap year).

Upvotes: 1

Related Questions