MySQL: Select All Dates In a Range Even If No Records Present

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) awhere 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().

This is better to do as:

-- 7 Days:set @n:=date(now() + interval 1 day);SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa    right join (        select (select @n:= @n - interval 1 day) day_series from tbl1 limit 7 ) as qb     on date(qa.Timestamp) = qb.day_series and qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc-- 30 Days:set @n:=date(now() + interval 1 day);SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa    right join (        select (select @n:= @n - interval 1 day) day_series from tbl1 limit 30 ) as qb     on date(qa.Timestamp) = qb.day_series and qa.Timestamp > DATE_SUB(curdate(), INTERVAL 30 day) order by qb.day_series asc;

or without variable like this:

SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qaright join (    select curdate() - INTERVAL a.a day as day_series from(        select 0 as a 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    ) as a ) as qbon date(qa.Timestamp) = qb.day_series andqa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc;

This question asks the same thing I think. Generally the accepted answer seems to be that you either do it in your application logic (read in what you have into an array, then loop through the array and create the missing dates), or you use temporary tables filled with the dates you wish to join.