MySQL: select all dates in a range, even if there are no records

Question

I have a user database. I would like to create a graph based on the growth of the user base. Now I have a request:

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 one day we get 0 users, this day will not be returned as the value 0, it is simply skipped, and the next day at least one user will be returned. How can I get something like (psuedo-response):

 date1 5 date2 8 date3 0 date4 0 date5 9 etc... 

where are dates with zero displayed in sequential order with the rest of the dates?

Thank!

Answer 1

I hope you understand 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 

FROM

 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 max n3 is 3 here, SELECT will return 399 plus 0 → 400 entries (dates on the calendar).

You can customize your dynamic calendar by restricting it, for example, to min (date), which you should now ().

Answer 2

This question asks the same as what I think. As a rule, the accepted answer seems to be that you either do this in your application logic (read what you have in the array, then iterate over the array and create the missing dates), or use temporary tables filled with dates, which you want to join.

Answer 3

It is better to do so:

 -- 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:

 SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa right join ( select curdate() - INTERVAL aa 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; 

Answer 4

Make the correct outer join to the table, name it tblCalendar, which is pre-populated with the dates you want to report. And join the date field.

Floor

Answer 5

With further thought, something like this should be what you want:

 CREATE TEMPORARY TABLE DateSummary1 ( datenew timestamp ) SELECT DISTINCT(DATE(datecreated)) as datenew FROM users; CREATE TEMPORARY TABLE DateSummary2 ( datenew timestamp, number int ) SELECT DATE(datecreated) as datenew, count(*) AS number FROM users WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW()) GROUP BY DATE(datecreated) ORDER BY datecreated ASC; SELECT ds1.datenew,ds2.number FROM DateSummary1 ds1 LEFT JOIN DateSummary2 ds2 on ds1.datenew=ds2.datenew; 

This gives you all the dates in the first table and the summary data count in the second table. You may need to replace ds2.number with IF(ISNULL(ds2.number),0,ds2.number) or something similar.

Answer 6

Inquiry:

 SELECT qb.dy as yourday, COALESCE(count(yourcolumn), 0) as yourcount from yourtable qa right join ( select curdate() as dy union select DATE_SUB(curdate(), INTERVAL 1 day) as dy union select DATE_SUB(curdate(), INTERVAL 2 day) as dy union select DATE_SUB(curdate(), INTERVAL 3 day) as dy union select DATE_SUB(curdate(), INTERVAL 4 day) as dy union select DATE_SUB(curdate(), INTERVAL 5 day) as dy union select DATE_SUB(curdate(), INTERVAL 6 day) as dy ) as qb on qa.dates = qb.dy and qa.dates > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.dy asc; 

and the result:

 +------------+-----------+ | yourday | yourcount | +------------+-----------+ | 2015-06-24 | 274339 | | 2015-06-25 | 0 | | 2015-06-26 | 0 | | 2015-06-27 | 0 | | 2015-06-28 | 134703 | | 2015-06-29 | 87613 | | 2015-06-30 | 0 | +------------+-----------+