How Can I Generate a Series of Non-Duplicate Dates in MySQL Without Using Temporary Tables or Variables?

Generating a Series of Dates (Non-Duplicate Approach)

Creating a series of dates within a specific range is a common task encountered in various programming applications. MySQL offers several methods for achieving this.

One efficient solution, especially when faced with restrictions like prohibited temporary table creation and variable setting, is to utilize a self-join query. This approach generates a list of dates within a specified time frame, as demonstrated in the following code snippet:

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 manipulating date values using the adddate() function and various subqueries representing days, months, and years.

This approach offers a concise and efficient method for generating a range of dates, particularly in situations where temporary table creation or variable setting are not feasible options.

The above is the detailed content of How Can I Generate a Series of Non-Duplicate Dates in MySQL Without Using Temporary Tables or Variables?. For more information, please follow other related articles on the PHP Chinese website!