mysql fill in missing dates

1MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -
2
3Create a table that only holds incrementing numbers - easy to do using an auto_increment:
4
5DROP TABLE IF EXISTS `example`.`numbers`;
6CREATE TABLE  `example`.`numbers` (
7  `id` int(10) unsigned NOT NULL auto_increment,
8   PRIMARY KEY  (`id`)
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10Populate the table using:
11
12INSERT INTO `example`.`numbers`
13  ( `id` )
14VALUES
15  ( NULL )
16...for as many values as you need.
17
18Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, YYYY-MM-DD) -
19
20SELECT `x`.*
21  FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
22          FROM `numbers` `n`
23         WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
24LEFT JOIN onto your table of data based on the time portion:
25
26   SELECT `x`.`ts` AS `timestamp`,
27          COALESCE(`y`.`score`, 0) AS `cnt`
28     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts`
29             FROM `numbers` `n`
30            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
31LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
32If you want to maintain the date format, use the DATE_FORMAT function:
33
34DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`