Best way to get recent date rows? (this week/month)

Question

I wanna get rows in recent date, this week or month, etc. suppose the table has a field named: product_date.

Answer 1

To get the rows in the last month, you could use something like:

SELECT * FROM table WHERE product_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);

Or for the last week:

SELECT * FROM table WHERE product_date >= DATE_SUB(NOW(), INTERVAL 1 WEEK);

Answer 2

For within the last seven days:

WHERE product_date BETWEEN DATE_ADD(NOW(), INTERVAL -7 DAY) AND NOW() 

For the last month:

WHERE product_date BETWEEN DATE_ADD(NOW(), INTERVAL -1 MONTH) AND NOW()

Answer 3

While mopoke's solution gets results from last 30 or 7 days, this gives you results for current month only:

SELECT * FROM table WHERE DATE_FORMAT(product_date, '%c-%Y') = DATE_FORMAT(CURDATE(), '%c-%Y');

Or for current week:

SELECT * FROM table WHERE DATE_FORMAT(product_date, '%u-%Y') = DATE_FORMAT(CURDATE(), '%u-%Y');