What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Question

I'm building a quick csv from a mysql table with a query like: ``` select DATE(date),count(date) from table group by DATE(date) order by date asc; ``` and just dumping them to a file in perl over a: ``` while(my($date,$sum) = $sth->fetchrow) { print CSV "$date,$sum
" } ``` There are date gaps in the data, though: ``` | 2008-08-05 | 4 | | 2008-08-07 | 23 | ``` I would like to pad the data to fill in the missing days with zero-count entries to end up with: ``` | 2008-08-05 | 4 | | 2008-08-06 | 0 | | 2008-08-07 | 23 | ``` I slapped together a really awkward (and almost certainly buggy) workaround with an array of days-per-month and some math, but there has to be something more straightforward either on the mysql or perl side. Any genius ideas/slaps in the face for why me am being so dumb? I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons: - I know the date range I'll be looking for every time - The server in ques…

Accepted Answer

When you need something like that on server side, you usually create a table which contains all possible dates between two points in time, and then left join this table with query results. Something like this:

```
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 particular case it would be better to put a little check on the client side, if current date is not previos+1, put some addition strings.

What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Solution

Problem