How to Generate a List of Dates Within a Specific Range Without Loops or Temporary Tables?

Generate date from date range

A common task in data analysis is to extract a specific date from a given date range. For example, you might need to retrieve dates from "2010-01-20" to "2010-01-24".

Solution without loops or temporary tables

To achieve this without using loops, procedures or temporary tables, we can leverage a subquery to generate a date sequence:

<code class="language-sql">SELECT a.Date
FROM (
    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
) a
WHERE a.Date BETWEEN '2010-01-20' AND '2010-01-24'</code>

This subquery generates a date series spanning approximately 10,000 days. You can adjust the number of days by expanding or contracting the range in the subquery.

Output

The query returns the following dates:

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

Performance Notes

The performance of this query is very good, running in 0.0009 seconds for a 5-day range. Even with a range of 100,000 days, it completes in just 0.0458 seconds.

Compatibility and Portability

This technology is compatible with most databases with only minor adjustments. For example, you might want to replace the CURDATE() function with the corresponding function in your specific database.

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