How to Retrieve a Date Range Using a MySQL Select Query?

Use MySQL SELECT statement to get the date range

Many applications require the ability to retrieve a list of dates within a specified range. In MySQL, there are several ways to achieve this, one of which is to use a SELECT statement.

For example, you need to list all dates between February 10, 2012 and February 15, 2012. To do this you can use the following query:

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 uses nested SELECT statements to generate a range of dates from '1970-01-01' to the current date. It then limits the generated dates to the specified range.

The advantage of this approach is that it is relatively simple to implement and can handle date ranges up to nearly 300 years in the future.

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