MySQL: Select All Dates In a Range Even If No Records Present
date, gaps-and-islands, mysql
Solution
I hope you will figure out the rest.
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
With
select n3.num*100+n2.num*10+n1.num as date
you will get a column with numbers from 0 to max(n3)*100+max(n2)*10+max(n1)
Since here we have max n3 as 3, SELECT will return 399, plus 0 -> 400 records (dates in calendar).
You can tune your dynamic calendar by limiting it, for example, from min(date) you have to now().
Problem
I have a database of users. I would like to create a graph based on userbase growth. The query I have now is: ``` 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 returns almost what I want. If we get 0 users one day, that day is not returned as a 0 value, it is just skipped and the next day that has at least one user is returned. How can I get something like (psuedo-response): ``` date1 5 date2 8 date3 0 date4 0 date5 9 etc... ``` where the dates with zero show up in sequential order with the rest of the dates? Thanks!