How can INTERVAL and CURDATE simplify date range queries in MySQL?

Working with INTERVAL and CURDATE in MySQL

In MySQL, you can utilize INTERVAL to simplify queries involving date ranges. Instead of writing multiple queries to retrieve data for different time periods, you can use INTERVAL to specify a range relative to the current date.

For instance, to retrieve data for the past 12 months, you can use the following query:

<code class="sql">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 > DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY s.GSP_nom
ORDER BY avg DESC</code>

In this query, the INTERVAL 12 MONTH specifies a range of 12 months relative to the current date. By using DATE_SUB to subtract this interval from CURDATE(), you define the start of the range.

Alternatively, you can use DATE_ADD to add an interval to the current date. For example, the following query retrieves data for the next 6 months:

<code class="sql">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 < DATE_ADD(CURDATE(), INTERVAL 6 MONTH)
GROUP BY s.GSP_nom
ORDER BY avg DESC</code>

By understanding the usage of INTERVAL, you can streamline your queries when working with date ranges in MySQL.

The above is the detailed content of How can INTERVAL and CURDATE simplify date range queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!