mySQL query : working with INTERVAL and CURDATE

Question

Hello,

i'm building a chart and i want to recieve data for each months

Here's my first request which is working :

SELECT s.GSP_nom AS nom, timestamp, AVG( v.vote +  v.prix  ) /2 AS avg
FROM votes_serveur AS v
INNER JOIN serveur AS s ON v.idServ = s.idServ
WHERE s.valide =1
AND v.date > CURDATE() -30
GROUP BY s.GSP_nom
ORDER BY avg DESC

But, in my case i've to write 12 request to recieve datas for the 12 previous months, is there any trick to avoid writing :

//  example for the previous month
 AND v.date > CURDATE() -60
AND v.date < CURDATE () -30

I heard about INTERVAL, i went to the mySQL doc but i didn't manage to implement it.

Any ideas / example of using INTERVAL please ?

Thank you

Answer 1

You need DATE_ADD/DATE_SUB:

AND v.date > (DATE_SUB(CURDATE(), INTERVAL 2 MONTH))
AND v.date < (DATE_SUB(CURDATE(), INTERVAL 1 MONTH))

should work.

Answer 2

I usually use

DATE_ADD(CURDATE(), INTERVAL - 1 MONTH)

Which is almost same as Pekka's but this way you can control your INTERVAL to be negative or positive...