How to Generate a Date Range in a MySQL SELECT Query?

Generate date range in MySQL SELECT query

This article describes how to generate a date list within a specified date range. For example, given a start date of '2012-02-10' and an end date of '2012-02-15', you should get the following result:

<code>日期      
----------
2012-02-10
2012-02-11
2012-02-12
2012-02-13
2012-02-14
2012-02-15 </code>

Solution

You can use the following query to achieve this:

SELECT * FROM 
(SELECT ADDDATE('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date FROM
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
 (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) v
WHERE selected_date BETWEEN '2012-02-10' AND '2012-02-15'

This query creates a virtual table by combining the values ​​of five subqueries (t0-t4). Each subquery generates a sequence of one-digit integers (0-9). These integers are then concatenated using the ADDDATE() function to form a date. The WHERE clause filters dates to retrieve dates within a specified range.

Note: This solution works for date ranges nearly 300 years into the future.

The above is the detailed content of How to Generate a Date Range in a MySQL SELECT Query?. For more information, please follow other related articles on the PHP Chinese website!