I'm building a chart and I want to receive data for each month.
Here's my first request which is working:
SELECT s.GSP_nom AS nom, timestamp, AVG( v.vote + v.prix ) /2 AS avgFROM votes_serveur AS vINNER JOIN serveur AS s ON v.idServ = s.idServWHERE s.valide =1AND v.date > CURDATE() -30GROUP BY s.GSP_nomORDER BY avg DESCBut, in my case I've to write 12 request to receive data for the 12 previous months, is there any trick to avoid writing:
// example for the previous monthAND v.date > CURDATE() -60AND v.date < CURDATE () -30I heard about INTERVAL, I went to the MySQL doc but i didn't manage to implement it.
Any example of using INTERVAL please?
Best Answer
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.
As suggested by A Star, I always use something along the lines of:
DATE(NOW()) - INTERVAL 1 MONTHSimilarly you can do:
NOW() + INTERVAL 5 MINUTE"2013-01-01 00:00:00" + INTERVAL 10 DAYand so on. Much easier than typing DATE_ADD or DATE_SUB all the time :)!
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...