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

Generating a Series of Dates without Temporary Tables or Variables

When seeking to generate a series of dates within a particular range for MySQL reporting, the optimal approach is crucial. This is especially pertinent in instances where temporary table creation and variable setting are prohibited.

One effective solution is to employ a combination of subqueries and union operations. Consider the following query:

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 generates a series of dates between '2017-01-01' and '2017-12-31' by combining the results of multiple subqueries. The subqueries create a table of numbers from 0 to 9, which are then used to construct the dates using the adddate() function. The final where clause filters the results to include only dates within the specified range.

By utilizing this method, you can efficiently generate a series of dates without the need for temporary tables or variables, making it suitable for situations with restricted permissions.

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