Working with INTERVAL and CURDATE in MySQL

Question

I'm building a chart and I need to receive the data for every month.

Following is 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

However, in my case I need to write 12 requests to receive the data for the 12 previous months, is there any method to avoid writing:

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

I heard about the INTERVAL, I went through the MySQL doc though I can't able to implement it.

Any example of using the INTERVAL, please?

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))

This will do the magic for you. 

Want to be a SQL expert? Join the Intellipaat's SQL Training program to learn more.