[mysql] Working with INTERVAL and CURDATE in MySQL

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?

This question is related to mysql datetime date intervals

The answer is


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


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


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to datetime

Comparing two joda DateTime instances How to format DateTime in Flutter , How to get current time in flutter? How do I convert 2018-04-10T04:00:00.000Z string to DateTime? How to get current local date and time in Kotlin Converting unix time into date-time via excel Convert python datetime to timestamp in milliseconds SQL Server date format yyyymmdd Laravel Carbon subtract days from current date Check if date is a valid one Why is ZoneOffset.UTC != ZoneId.of("UTC")?

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to intervals

How do you create vectors with specific intervals in R? How can I plot data with confidence intervals? PHP: How can I determine if a variable has a value that is between two distinct constant values? Working with INTERVAL and CURDATE in MySQL How do I convert an interval into a number of hours with postgres?