Home >Database >Mysql Tutorial >How to Retrieve All Dates Within a Specified Range in MySQL, Including Dates with No Records?
When working with databases, it's often necessary to retrieve data for a specific period or range. However, if there are no records for certain dates within that range, those dates may be skipped in the results. This can lead to gaps in data analysis and visualizations.
Problem:
In MySQL, the query below selects the dates and corresponding counts from the users table within a given date range:
SELECT DATE(datecreated), count(*) AS number FROM users WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW()) GROUP BY DATE(datecreated) ORDER BY datecreated ASC
This query returns results such as:
date1 5 date2 8 date5 9
However, it skips dates with zero users, leaving gaps in the data. The desired output would be:
date1 5 date2 8 date3 0 date4 0 date5 9
Solution:
To retrieve all dates in the specified range, even if there are no associated records, we can use a sophisticated query that leverages generated date sequences. The query below accomplishes this:
select * from ( select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from (select 0 as num 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) n1, (select 0 as num 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) n2, (select 0 as num 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) n3, (select 0 as num 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) n4, (select 0 as num 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) n5 ) a where date >'2011-01-02 00:00:00.000' and date < NOW() order by date
This query uses nested UNION ALL subqueries to generate a sequence of dates ranging from January 1, 2003, to just before the current date. The date_add() function is then used to incrementally add the generated dates to the starting date to create the desired date range.
Additional Notes:
The above is the detailed content of How to Retrieve All Dates Within a Specified Range in MySQL, Including Dates with No Records?. For more information, please follow other related articles on the PHP Chinese website!