Keywords: MySQL | Date Generation | SQL Query
Abstract: This article explains how to generate a list of all dates between two specified dates in a MySQL query. By analyzing the SQL code from the best answer, it uses the ADDDATE function with subqueries to create a number sequence and filters using a WHERE clause for efficient date range generation. The article provides an in-depth breakdown of each component and discusses advantages, limitations, and use cases.
Introduction
In database applications, there is often a need to generate a sequence of all dates between two given dates, such as for reporting or data analysis. MySQL lacks a built-in function for this, requiring creative SQL solutions.
Core Solution: Using ADDDATE and Number Generation
The accepted answer provides an effective method to generate dates. The key idea is to create a series of numbers through subqueries and convert them to dates using the ADDDATE function.
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i 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 i 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 i 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 i 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 i 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 selected_date between '2012-02-10' and '2012-02-15'
This query works by generating a Cartesian product of five subqueries, each producing numbers from 0 to 9, resulting in up to 100,000 numbers. These are added as days to a base date (e.g., '1970-01-01') using ADDDATE to create a date sequence. The outer query filters the dates to the specified range with a WHERE clause.
Detailed Analysis
Breaking down the components:
- Number Generation: Subqueries t0 to t4 each generate digits 0-9, combining to produce numbers from 0 to 99999.
- Date Conversion:
ADDDATE('1970-01-01', number)converts numbers into dates, covering nearly 300 years. - Filtering: The
WHEREclause limits output to the desired date range.
This approach is versatile and compatible with older MySQL versions that do not support recursive CTEs.
Advantages and Limitations
Advantages: Efficient for large date ranges, highly compatible, and flexible with base date selection.
Limitations: The query can be complex for beginners, and performance may degrade with extremely large ranges, though 100,000 dates suffice for most applications.
Conclusion
Due to the absence of native date sequence generation in MySQL, the method described here offers a robust solution through date arithmetic and subquery joins. For MySQL 8.0+, recursive CTEs provide an alternative, but this technique remains valuable for backward compatibility.