How to Generate a Date Series Without Temporary Tables or Variables?

Generating a Series of Dates without Temporary Tables or Variables

Despite the availability of tables with predefined date ranges, this approach is deemed suboptimal. How can we generate a series of dates within a specified interval without resorting to temporary tables or variable manipulation?

Consider the following scenario: your application requires a report that returns a row for each date, regardless of available data. To achieve this, a simple query can be constructed:

select dates.date, sum(sales.amount)
from <series of dates between X and Y> dates
left join sales on date(sales.created) = dates.date
group by 1

To avoid creating a table with numerous dates, an alternative solution is recommended:

select * from 
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
 (select 0 t0 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 t1 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 t2 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 t3 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 t4 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 gen_date between '2017-01-01' and '2017-12-31'

This query effectively generates a series of dates within the specified range without creating a separate table or setting variables.

The above is the detailed content of How to Generate a Date Series Without Temporary Tables or Variables?. For more information, please follow other related articles on the PHP Chinese website!