Select the maximal and minimum dates in the last 3 months

in my my mysql table, I have a column with date format: yyyy-mm-dd

I make this request to select the maximum and minimum dates for the last 2 months.

SELECT MIN(date) AS date1, MAX(date) AS date2 FROM mytable
    WHERE (MONTH(date) = (SELECT Month(MAX(date))-1 FROM mytable )
    OR MONTH(date) = (SELECT Month(MAX(date))-2 FROM mytable ))
    AND YEAR(date) = (SELECT YEAR(MAX(date))
    FROM mytable)

But when I want to select dates for the last 3 months, I found that I can't make the month Jaunuar-1 and also the request :

OR MONTH(date) = 开发者_开发百科(SELECT Month(MAX(date))-3 FROM mytable )

don't work !

My question is: How can I select the last 3 Months (or the maximal and minimum dates in the last 3 or 4 or 5 months).

Thanks a lot.

---

SELECT MIN(`date`) AS date1,
       MAX(`date`) AS date2 
  FROM mytable
 WHERE `date` BETWEEN now() - interval 3 month 
                  AND now();

---

SELECT MIN(date) AS date1, MAX(date) AS date2 
FROM mytable
WHERE date >= date_sub((select max(date) from mytable), month, 3) 

---

It appears that you want the complete three months, that is, since today is March 4th you'd want anything from December 1st to now, rather than from December 4th to now.

The below query should do that:

SELECT MIN(date), MAX(date) FROM mytable 
WHERE date <= DATE(NOW()) AND 
      date >= DATE_SUB(DATE_SUB(DATE(NOW()), 
                                INTERVAL DAY(NOW())-1 DAY), 
                       INTERVAL 3 MONTH)

Similarly, if you wanted the previous 3 months non-inclusive of this month, e.g. December 1 to Feb 28th you could adapt the where clause:

WHERE date <= DATE_SUB(DATE(NOW()), INTERVAL DAY(NOW()) DAY) AND date >= ...