I have an SQL query that retrieves the last 7 months worth of payments, calculates the sum, and groups them by their correlating month...
The only problem with the query is that for some reason on the 31st of every month, it messes up. To be less vague, instead of picking up the last 7 months (should exclude the current month), it picks up the current month as if it was the last month (as if we are already in January and when todays actual date is 31st of December).
SELECT DATE(date) AS month, SUM(amount_paid) AS amount FROM payments WHERE date > DATE_SUB(NOW(), INTERVAL 7 MONTH) GROUP BY YEAR(date), MONTH(date)");
So to break this down, the result should look like this:
0 => 2013-05-01
1 => 2013-06-03
2 => 2013-07-01
3 => 2013-08-01
4 => 2013-09-02
5 => 2013-10-01
6 => 2013-11-01
However, the result looks like so:
0 => 2013-06-03
1 => 2013-07-01
2 => 2013-08-01
3 => 2013-09-02
4 => 2013-10-01
5 => 2013-11-01
6 => 2013-12-02
I'd like to say I found a bug with MySQL date_sub, but I'm getting the sense there is an error somewhere in the query. And again, today is December 31st and it only happens ON the 31st of any given month.
Cheers!
So I figured it out courtesy to the following answer: Get the values for last 6 months in mysql
Here is what my MYSQL query looks like now. It's messy and I feel that DATE_SUB should still do the job but nevertheless:
SELECT DATE(date) AS month, SUM(amount_paid) AS amount
FROM
payments
WHERE
DATE_FORMAT(date,'%Y-%m') < DATE_FORMAT(NOW(),'%Y-%m')
AND
DATE_FORMAT(date,'%Y-%m') >= DATE_FORMAT(NOW() - INTERVAL 7 MONTH,'%Y-%m')
GROUP BY
YEAR(date), MONTH(date)
Still working on an answer for you, but I wonder if it as anything to do with this:
"If you add MONTH, YEAR_MONTH, or YEAR and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month"
mysql> SELECT DATE_ADD('2009-01-30', INTERVAL 1 MONTH); -> '2009-02-28'
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add