<p>I'm building a quick csv from a mysql table with a query like:</p>

<pre><code>select DATE(date),count(date) from table group by DATE(date) order by date asc;
</code></pre>

<p>and just dumping them to a file in perl over a:</p>

<pre><code>while(my($date,$sum) = $sth-&gt;fetchrow) {
    print CSV "$date,$sum\n"
}
</code></pre>

<p>There are date gaps in the data, though:</p>

<pre><code>| 2008-08-05 |           4 | 
| 2008-08-07 |          23 | 
</code></pre>

<p>I would like to pad the data to fill in the missing days with zero-count entries to end up with:</p>

<pre><code>| 2008-08-05 |           4 | 
| 2008-08-06 |           0 | 
| 2008-08-07 |          23 | 
</code></pre>

<p>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. </p>

<p>Any genius ideas/slaps in the face for why me am being so dumb?</p>

<hr>

<p>I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons:</p>

<ul>
<li>I know the date range I'll be looking for every time</li>
<li>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</li>
</ul>

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

[{'answer_id': 75807, 'author': 'Aeon', 'author_id': 13289, 'author_profile': 'https://Stackoverflow.com/users/13289', 'pm_score': 3, 'selected': False, 'text': '<p>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. </p>\n\n<pre><code>SELECT DATE(r.date),count(d.date) \nFROM dates AS r \nLEFT JOIN table AS d ON d.date = r.date \nGROUP BY DATE(r.date) \nORDER BY r.date ASC;\n</code></pre>\n\n<p>As for output, I\'d just use <a href="http://dev.mysql.com/doc/refman/5.0/en/select.html" rel="noreferrer">SELECT INTO OUTFILE</a> instead of generating the CSV by hand. Leaves us free from worrying about escaping special characters as well.</p>\n'}, {'answer_id': 75837, 'author': 'Ben', 'author_id': 11522, 'author_profile': 'https://Stackoverflow.com/users/11522', 'pm_score': -1, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 75865, 'author': 'coffeepac', 'author_id': 13421, 'author_profile': 'https://Stackoverflow.com/users/13421', 'pm_score': 2, 'selected': False, 'text': '<p>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...</p>\n\n<pre><code>my $current_date = Date::EzDate-&gt;new();\n$current_date-&gt;{\'default\'} = \'{YEAR}-{MONTH NUMBER BASE 1}-{DAY OF MONTH}\';\nwhile ($current_date &lt;= $final_date)\n{\n    print "$current_date\\t|\\t%hash_o_data{$current_date}";  # EzDate provides for     automatic stringification in the format specfied in \'default\'\n    $current_date++;\n}\n</code></pre>\n\n<p>where final date is another EzDate object or a string containing the end of your date range.  </p>\n\n<p>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.  </p>\n'}, {'answer_id': 75890, 'author': 'Alexandr Ciornii', 'author_id': 13467, 'author_profile': 'https://Stackoverflow.com/users/13467', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 75928, 'author': 'GSerg', 'author_id': 11683, 'author_profile': 'https://Stackoverflow.com/users/11683', 'pm_score': 5, 'selected': True, 'text': '<p>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:</p>\n\n<pre class="lang-sql prettyprint-override"><code>create procedure sp1(d1 date, d2 date)\n  declare d datetime;\n\n  create temporary table foo (d date not null);\n\n  set d = d1\n  while d &lt;= d2 do\n    insert into foo (d) values (d)\n    set d = date_add(d, interval 1 day)\n  end while\n\n  select foo.d, count(date)\n  from foo left join table on foo.d = table.date\n  group by foo.d order by foo.d asc;\n\n  drop temporary table foo;\nend procedure\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 76081, 'author': '8jean', 'author_id': 10011, 'author_profile': 'https://Stackoverflow.com/users/10011', 'pm_score': 2, 'selected': False, 'text': '<p>You could use a <a href="http://search.cpan.org/perldoc?DateTime" rel="nofollow noreferrer">DateTime</a> object:</p>\n\n<pre><code>use DateTime;\nmy $dt;\n\nwhile ( my ($date, $sum) = $sth-&gt;fetchrow )  {\n    if (defined $dt) {\n        print CSV $dt-&gt;ymd . ",0\\n" while $dt-&gt;add(days =&gt; 1)-&gt;ymd lt $date;\n    }\n    else {\n        my ($y, $m, $d) = split /-/, $date;\n        $dt = DateTime-&gt;new(year =&gt; $y, month =&gt; $m, day =&gt; $d);\n    }\n    print CSV, "$date,$sum\\n";\n}\n</code></pre>\n\n<p>What the above code does is it keeps the last printed date stored in a\n<code>DateTime</code> object <code>$dt</code>, and when the current date is more than one day\nin the future, it increments <code>$dt</code> by one day (and prints it a line to\n<code>CSV</code>) until it is the same as the current date.</p>\n\n<p>This way you don\'t need extra tables, and don\'t need to fetch all your\nrows in advance.</p>\n'}, {'answer_id': 76147, 'author': 'castaway', 'author_id': 4840, 'author_profile': 'https://Stackoverflow.com/users/4840', 'pm_score': 1, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>use DateTime;\nuse DateTime::Format::Strptime;\nmy @row = $sth-&gt;fetchrow;\nmy $countdate = strptime("%Y-%m-%d", $firstrow[0]);\nmy $thisdate = strptime("%Y-%m-%d", $firstrow[0]);\n\nwhile ($countdate) {\n  # keep looping countdate until it hits the next db row date\n  if(DateTime-&gt;compare($countdate, $thisdate) == -1) {\n    # counter not reached next date yet\n    print CSV $countdate-&gt;ymd . ",0\\n";\n    $countdate = $countdate-&gt;add( days =&gt; 1 );\n    $next;\n  }\n\n  # countdate is equal to next row\'s date, so print that instead\n  print CSV $thisdate-&gt;ymd . ",$row[1]\\n";\n\n  # increase both\n  @row = $sth-&gt;fetchrow;\n  $thisdate = strptime("%Y-%m-%d", $firstrow[0]);\n  $countdate = $countdate-&gt;add( days =&gt; 1 );\n}\n</code></pre>\n\n<p>Hmm, that turned out to be more complicated than I thought it would be.. I hope it makes sense!</p>\n'}, {'answer_id': 6156000, 'author': 'theazureshadow', 'author_id': 177633, 'author_profile': 'https://Stackoverflow.com/users/177633', 'pm_score': 1, 'selected': False, 'text': "<p>I think the simplest general solution to the problem would be to create an <code>Ordinal</code> table with the highest number of rows that you need (in your case 31*3 = 93).</p>\n\n<pre><code>CREATE TABLE IF NOT EXISTS `Ordinal` (\n  `n` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`n`)\n);\nINSERT INTO `Ordinal` (`n`)\nVALUES (NULL), (NULL), (NULL); #etc\n</code></pre>\n\n<p>Next, do a <code>LEFT JOIN</code> from <code>Ordinal</code> onto your data. Here's a simple case, getting every day in the last week:</p>\n\n<pre><code>SELECT CURDATE() - INTERVAL `n` DAY AS `day`\nFROM `Ordinal` WHERE `n` &lt;= 7\nORDER BY `n` ASC\n</code></pre>\n\n<p>The two things you would need to change about this are the starting point and the interval. I have used <code>SET @var = 'value'</code> syntax for clarity.</p>\n\n<pre><code>SET @end = CURDATE() - INTERVAL DAY(CURDATE()) DAY;\nSET @begin = @end - INTERVAL 3 MONTH;\nSET @period = DATEDIFF(@end, @begin);\n\nSELECT @begin + INTERVAL (`n` + 1) DAY AS `date`\nFROM `Ordinal` WHERE `n` &lt; @period\nORDER BY `n` ASC;\n</code></pre>\n\n<p>So the final code would look something like this, if you were joining to get the number of messages per day over the last three months:</p>\n\n<pre><code>SELECT COUNT(`msg`.`id`) AS `message_count`, `ord`.`date` FROM (\n    SELECT ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH) + INTERVAL (`n` + 1) DAY AS `date`\n    FROM `Ordinal`\n    WHERE `n` &lt; (DATEDIFF((CURDATE() - INTERVAL DAY(CURDATE()) DAY), ((CURDATE() - INTERVAL DAY(CURDATE()) DAY) - INTERVAL 3 MONTH)))\n    ORDER BY `n` ASC\n) AS `ord`\nLEFT JOIN `Message` AS `msg`\n  ON `ord`.`date` = `msg`.`date`\nGROUP BY `ord`.`date`\n</code></pre>\n\n<p>Tips and Comments:</p>\n\n<ul>\n<li>Probably the hardest part of your query was determining the number of days to use when limiting <code>Ordinal</code>. By comparison, transforming that integer sequence into dates was easy.</li>\n<li>You can use <code>Ordinal</code> for all of your uninterrupted-sequence needs. Just make sure it contains more rows than your longest sequence.</li>\n<li>You can use multiple queries on <code>Ordinal</code> for multiple sequences, for example listing every weekday (1-5) for the past seven (1-7) weeks.</li>\n<li>You could make it faster by storing dates in your <code>Ordinal</code> table, but it would be less flexible. This way you only need one <code>Ordinal</code> table, no matter how many times you use it. Still, if the speed is worth it, try the <code>INSERT INTO ... SELECT</code> syntax.</li>\n</ul>\n"}, {'answer_id': 16318007, 'author': 'Igor Kryltsov', 'author_id': 1051674, 'author_profile': 'https://Stackoverflow.com/users/1051674', 'pm_score': 2, 'selected': False, 'text': "<p>I hope you will figure out the rest.</p>\n\n<pre><code>select  * from (\nselect date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from\n(select 0 as num\n   union all select 1\n   union all select 2\n   union all select 3\n   union all select 4\n   union all select 5\n   union all select 6\n   union all select 7\n   union all select 8\n   union all select 9) n1,\n(select 0 as num\n   union all select 1\n   union all select 2\n   union all select 3\n   union all select 4\n   union all select 5\n   union all select 6\n   union all select 7\n   union all select 8\n   union all select 9) n2,\n(select 0 as num\n   union all select 1\n   union all select 2\n   union all select 3\n   union all select 4\n   union all select 5\n   union all select 6\n   union all select 7\n   union all select 8\n   union all select 9) n3,\n(select 0 as num\n   union all select 1\n   union all select 2\n   union all select 3\n   union all select 4\n   union all select 5\n   union all select 6\n   union all select 7\n   union all select 8\n   union all select 9) n4,\n(select 0 as num\n   union all select 1\n   union all select 2\n   union all select 3\n   union all select 4\n   union all select 5\n   union all select 6\n   union all select 7\n   union all select 8\n   union all select 9) n5\n) a\nwhere date &gt;'2011-01-02 00:00:00.000' and date &lt; NOW()\norder by date\n</code></pre>\n\n<p>With </p>\n\n<pre><code>select n3.num*100+n2.num*10+n1.num as date\n</code></pre>\n\n<p>you will get a column with numbers from 0 to max(n3)*100+max(n2)*10+max(n1)</p>\n\n<p>Since here we have max n3 as 3, SELECT will return 399, plus 0 -> 400 records (dates in calendar).</p>\n\n<p>You can tune your dynamic calendar by limiting it, for example, from min(date) you have to now(). </p>\n"}]
['https://Stackoverflow.com/questions/75752', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/13196/']