CODEWITHSUNDEEP
mysqldate
sys_debug
sys_debug

Reputation: 4003

mysql select dates in 30-day range

This must be simple but I fiddled with it, and didn't get anything I wanted. I have the following code:

SELECT id,title,start_date 
  FROM events 
 WHERE start_date > DATE_SUB(NOW(), INTERVAL 1 MONTH) 
  AND city = '$cityName' 
ORDER BY start_date DESC

Now this selects events with dates in this month, but the definition of this month shown in query is different than what I need. I need it to show me events within 30 days and not only this month i.e. august. If I insert an event in august it shows the outcome. If I do insert september, it doesn't even though it is less than 30 days away.

Upvotes: 11

Views: 48427

Answers (4)

Humphrey
Humphrey

Reputation: 2817

I hope this will help also 

SELECT id,title,start_date 
  FROM events 
 WHERE  city = "$cityName" AND 
TIMESTAMPDIFF(DAY,start_date,now()) < 30   
ORDER BY start_date DESC

Upvotes: 1

d4c0d312
d4c0d312

Reputation: 783

OR

AND TIMESTAMPDIFF(DAY,YOURDATE,now()) < 30

This gives you a 30 day span

Upvotes: 2

Scott Presnell
Scott Presnell

Reputation: 1538

How about like this:

...WHERE DATE(start_date) BETWEEN DATE_SUB(NOW(),INTERVAL 30 DAY) and DATE_SUB(NOW(),INTERVAL 1 DAY) AND city...

Upvotes: 3

Mark Byers
Mark Byers

Reputation: 839054

You should change 1 MONTH to 30 DAY:

WHERE start_date > NOW() - INTERVAL 30 DAY

To limit it to 30 days in either direction:

WHERE start_date > NOW() - INTERVAL 30 DAY
AND start_date < NOW() + INTERVAL 30 DAY

Upvotes: 57

Related Questions