I have a WHERE clause that selects all the rows where the date column is within the current month.
"WHERE date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND LAST_DAY(CURDATE())"
How can i make it select posts that are X months in the past or X months in the future?
For last month, a couple possibilities to try/compare:
WHERE YEAR(date) = YEAR(CURDATE() - INTERVAL 1 MONTH) AND MONTH(date) = MONTH(CURDATE() - INTERVAL 1 MONTH)
WHERE DATE_FORMAT(date, '%Y%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y%m')
Or how about
WHERE `date` >= DATE_SUB(CURDATE(), INTERVAL X MONTH)
