How Can I Efficiently Generate a Sequence of Dates Within a Specific Range in SQL?

Efficient method to generate SQL date range

In database programming, generating a date sequence within a specific date range is a common task. For example, query the dates between January 20, 2010 and January 24, 2010:

<code class="language-sql">SELECT ... AS days WHERE `date` BETWEEN '2010-01-20' AND '2010-01-24'</code>

The expected result is:

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

Efficient subquery solution

An efficient solution is to use 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>

Performance Considerations

This solution is extremely performant, with query execution time of only 0.0009 seconds. Even generating 100,000 dates (about 274 years), the query execution time is only 0.0458 seconds.

Portability

This technology is highly portable and can be adapted to most database systems with only minor adjustments.

The above is the detailed content of How Can I Efficiently Generate a Sequence of Dates Within a Specific Range in SQL?. For more information, please follow other related articles on the PHP Chinese website!