50

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 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 receive data 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 example of using INTERVAL please?

halfer
  • 19,824
  • 17
  • 99
  • 186
sf_tristanb
  • 8,725
  • 17
  • 74
  • 118

3 Answers3

81

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.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
31

As suggested by A Star, I always use something along the lines of:

DATE(NOW()) - INTERVAL 1 MONTH

Similarly you can do:

NOW() + INTERVAL 5 MINUTE
"2013-01-01 00:00:00" + INTERVAL 10 DAY

and so on. Much easier than typing DATE_ADD or DATE_SUB all the time :)!

seddy
  • 801
  • 8
  • 16
17

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

confiq
  • 2,795
  • 1
  • 26
  • 43