<p>I'm building a quick csv from a mysql table using a query like: </p> <pre class="brush:php;toolbar:false;">select DATE(date),count(date) from table group by DATE(date) order by date asc;</pre> <p>Then dump them directly into a file in perl: </p> <pre class="brush:php;toolbar:false;">while(my($date,$sum) = $sth->fetchrow) { print CSV "$date,$sumn" }</pre> <p>However, there are date gaps in the data: </p> <pre class="brush:php;toolbar:false;">| 2008-08-05 | 4 | | 2008-08-07 | 23 |</pre> <p>I want to pad the data, filling in missing dates with zero-count entries, and end up with: </p> <pre class="brush:php;toolbar:false;">| 2008-08-05 | 4 | | 2008-08-06 | 0 | | 2008-08-07 | 23 |</pre> <p>I cobbled together a very clumsy (almost certainly buggy) workaround using an array of days in the month and some math, but there's definitely a more direct way, either in mysql or perl. </p> <p>Any genius ideas/suggestions on why I’m so stupid? </p> <hr /> <p>In the end I chose to use a stored procedure to generate a temporary table to handle the required date range for several reasons: </p> <ul> <li>I know the date range to look for every time</li> <li>Unfortunately, this server cannot currently install perl modules, and it is in such bad shape that it does not have anything like Date::-y installed</li> </ul> <p>The date/time iteration in perl answer is also very good, I wish I could select multiple answers! </p>

SELECT DATE(r.date),count(d.date) 
FROM dates AS r 
LEFT JOIN table AS d ON d.date = r.date 
GROUP BY DATE(r.date) 
ORDER BY r.date ASC;

create procedure sp1(d1 date, d2 date)
  declare d datetime;

  create temporary table foo (d date not null);

  set d = d1
  while d <= d2 do
    insert into foo (d) values (d)
    set d = date_add(d, interval 1 day)
  end while

  select foo.d, count(date)
  from foo left join table on foo.d = table.date
  group by foo.d order by foo.d asc;

  drop temporary table foo;
end procedure