CODEWITHSUNDEEP
mysqldatabase
boisterouslobster
boisterouslobster

Reputation: 1293

Grabbing date records that are exactly a month or more away

I put together a query for determining whether the time difference between the current date and a record from a database is exactly a month or more apart. I am comparing now() to a created_at column, which is a timestamp.

EX: 6-12-2014, 7-12-2014

AND

5-12-2014, 7-12-2014

Should be considered to be a desirable results.

SELECT count(*) FROM `subscriptions` WHERE 
DATE_ADD(CAST(created_at as DATE),INTERVAL TIMESTAMPDIFF(MONTH, created_at, now()) MONTH)    = CAST(now() as DATE);

However the query appears to not return all desired results. It returns 2-28-2014 and 7-28-2014, however it does not pull up 6-28-2014. Is there a better way of doing this than the solution I came up with?

Upvotes: 0

Views: 35

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26353

Are you looking to count dates that are on the same day of the month as the current date? If so, try the DAYOFMONTH function:

SELECT COUNT(*)
FROM subscriptions
WHERE DAYOFMONTH(created_at) = DAYOFMONTH(NOW())

Upvotes: 1

Related Questions