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
) 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().
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 qa
right 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 qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc;