How to Efficiently Generate a Date Range in SQL?

SQL date range generation skills

There are many ways to generate a list of dates within a specified range in SQL. This article takes generating the date sequence from '2010-01-20' to '2010-01-24' as an example to introduce an efficient solution.

This method uses a subquery to generate a complete date collection. Through the combination of four nested loops, the subquery can efficiently generate date sequences up to 10,000 days and can be easily extended to longer date ranges.

<code class="language-sql">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</code>

We then filter the generated date list to extract dates within the target range:

<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 (...) as a
) a
where a.Date between '2010-01-20' and '2010-01-24'</code>

The advantages of this approach are: no loops, stored procedures or temporary tables required, efficient and portable to multiple database platforms. Generating 10,000 days of dates takes less than 0.01 seconds, and the performance is still excellent even when generating dates of about 100,000 days.

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