在MySQL中获取最后X个日期

Question

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!

Answer 1

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)