Working with INTERVAL and CURDATE in MySQL

Question

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  gt  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  gt  CURDATE   -60 AND v date  lt  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

User · Answer

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

User · Answer

You need DATE ADD DATE SUB   AND v date  gt   DATE SUB CURDATE    INTERVAL 2 MONTH   AND v date  lt   DATE SUB CURDATE    INTERVAL 1 MONTH     should work

User · Answer

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

[mysql] Working with INTERVAL and CURDATE in MySQL

Examples related to mysql

Examples related to datetime

Examples related to date

Examples related to intervals