How to Generate a List of Dates Within a Specific Range Using SQL?

Use SQL to generate a list of dates within a date range

In data analysis and management, it is often necessary to generate a date list within a specified date range. This can be achieved through efficient SQL queries.

One way is to use a subquery to generate a date sequence. For example, the following query generates a list of dates for the past 10,000 days:

SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY AS Date
FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS d;

To generate a list of dates within a specific date range, you can filter the dates using the WHERE clause:

SELECT a.Date
FROM (
    SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)) DAY AS Date
    FROM ... -- 上述子查询
) a
WHERE a.Date BETWEEN '2010-01-20' AND '2010-01-24';

This query will return the following output:

<code>Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20</code>

The performance of this method is surprisingly good, taking less than a second to generate 100,000 dates. It is also highly portable and compatible with most databases.

The above is the detailed content of How to Generate a List of Dates Within a Specific Range Using SQL?. For more information, please follow other related articles on the PHP Chinese website!