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\n"
}

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 question unfortunately was not one that I can install perl modules on atm, and the state of it was decrepit enough that it didn't have anything remotely Date::-y installed

The perl Date/DateTime-iterating answers were also very good, I wish I could select multiple answers!

Answer 1

When I had to deal with this problem, to fill in missing dates I actually created a reference table that just contained all dates I'm interested in and joined the data table on the date field. It's 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 output, I'd just use SELECT INTO OUTFILE instead of generating the CSV by hand. Leaves us free from worrying about escaping special characters as well.

Answer 2

I don't know if this would work, but how about if you created a new table which contained all the possible dates (that might be the problem with this idea, if the range of dates is going to change unpredictably...) and then do a left join on the two tables? I guess it's a crazy solution if there are a vast number of possible dates, or no way to predict the first and last date, but if the range of dates is either fixed or easy to work out, then this might work.

Answer 3

not dumb, this isn't something that MySQL does, inserting the empty date values. I do this in perl with a two-step process. First, load all of the data from the query into a hash organised by date. Then, I create a Date::EzDate object and increment it by day, so...

my $current_date = Date::EzDate->new();
$current_date->{'default'} = '{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}';
while ($current_date <= $final_date)
{
    print "$current_date\t|\t%hash_o_data{$current_date}";  # EzDate provides for     automatic stringification in the format specfied in 'default'
    $current_date++;
}

where final date is another EzDate object or a string containing the end of your date range.

EzDate isn't on CPAN right now, but you can probably find another perl mod that will do date compares and provide a date incrementor.

Answer 4

The MySQL approach would be to outer-join it with a list of all possible dates. I'm not sure if there is an easy way to generate that on the fly, otherwise you'd need a new table to hold those dates.

In Perl, you could check if the date you got is one day after the last one you got and if not, insert it with a value of zero.

Answer 5

Use some Perl module to do date calculations, like recommended DateTime or Time::Piece (core from 5.10). Just increment date and print date and 0 until date will match current.

Answer 6

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.

Answer 7

You could use a DateTime object:

use DateTime;
my $dt;

while ( my ($date, $sum) = $sth->fetchrow )  {
    if (defined $dt) {
        print CSV $dt->ymd . ",0\n" while $dt->add(days => 1)->ymd lt $date;
    }
    else {
        my ($y, $m, $d) = split /-/, $date;
        $dt = DateTime->new(year => $y, month => $m, day => $d);
    }
    print CSV, "$date,$sum\n";
}

What the above code does is it keeps the last printed date stored in a DateTime object $dt, and when the current date is more than one day in the future, it increments $dt by one day (and prints it a line to CSV) until it is the same as the current date.

This way you don't need extra tables, and don't need to fetch all your rows in advance.

Answer 8

Since you don't know where the gaps are, and yet you want all the values (presumably) from the first date in your list to the last one, do something like:

use DateTime;
use DateTime::Format::Strptime;
my @row = $sth->fetchrow;
my $countdate = strptime("%Y-%m-%d", $firstrow[0]);
my $thisdate = strptime("%Y-%m-%d", $firstrow[0]);

while ($countdate) {
  # keep looping countdate until it hits the next db row date
  if(DateTime->compare($countdate, $thisdate) == -1) {
    # counter not reached next date yet
    print CSV $countdate->ymd . ",0\n";
    $countdate = $countdate->add( days => 1 );
    $next;
  }

  # countdate is equal to next row's date, so print that instead
  print CSV $thisdate->ymd . ",$row[1]\n";

  # increase both
  @row = $sth->fetchrow;
  $thisdate = strptime("%Y-%m-%d", $firstrow[0]);
  $countdate = $countdate->add( days => 1 );
}

Hmm, that turned out to be more complicated than I thought it would be.. I hope it makes sense!

Answer 9

I upvoted the store procedure one. but RUN FROM IT.

create a temporary table with the dates alright. but do not use the stored procs.