How do I populate empty dates in SQL results, either on the MySQL or Perl side, and what's the most straightforward way?

Question

I'm building a quick csv from a mysql table using a query like:

select DATE(date),count(date) from table group by DATE(date) order by date asc;

Then dump them directly into a file in perl:

while(my($date,$sum) = $sth->fetchrow) {
    print CSV "$date,$sumn"
}

However, there are date gaps in the data:

| 2008-08-05 | 4 |
| 2008-08-07 | 23 |

I want to pad the data, filling in missing dates with zero-count entries, and end up with:

| 2008-08-05 | 4 |
| 2008-08-06 | 0 |
| 2008-08-07 | 23 |

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.

Any genius ideas/suggestions on why I’m so stupid?

---

In the end I chose to use a stored procedure to generate a temporary table to handle the required date range for several reasons:

• I know the date range to look for every time
• 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

The date/time iteration in perl answer is also very good, I wish I could select multiple answers!

Answer 1

When I needed to deal with this problem, to fill in the missing dates, I actually created a reference table containing just all the dates I was interested in and joined the data tables on the date fields. It's crude and crude, but it works.

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;

As for the output, I would use SELECT INTO OUTFILE instead of manually generating the CSV. This way we don't have to worry about escaping special characters.

Answer 2

When you need something similar on the server side, you usually create a table containing all possible dates between two points in time, and then do a left join to this table with the query results. Something similar to the following:

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

In this specific case, it would be better to do some check on the client side and add some additional string if the current date is not the day after the previous day.