MySQL - select missing dates from table?

Question

Suppose you have the following table values:

date       | value
2012-01-01 |    8
2012-01-02 |    3
2012-01-03 |   17
2012-01-09 |  100
2012-01-12 |    2

Now suppose you want to select all the dates between 2012-01-02 and 2012-01-12 and show their values if present. If you simply query the table for the appropriate date range, the dates that don't have values are going to be absent, for obvious reasons. Is there a way to fill in those dates in the query?

The obvious solution is to create a table dates that just stores a list of all dates that may come up, and then to select from the dates table and join values to it, but I'd like to have a solution that doesn't rely on creating a single-column table if I can.

Of note: there are existing questions on SO on this topic, but they are all from 2010 (at least the ones I found when searching were), and MySQL features have grown in that time; there may be a dynamic solution now. If that's not the case, and the dates table is still the best solution, then this question should be closed as a duplicate.

Possible duplicate of http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range ... the answers are all about using a calendar table, which the requester specifically asked about *not* using, but the answers also assert that there is no other way. — Martin Atkins, Mar 30 '13 at 05:58

score 0 · Accepted Answer · answered Apr 05 '13 at 12:58

The lack of answers from others suggests to me that at the current time, it is not possible to traverse a range of dates in MySQL without a table that holds those dates. I have, however, written some code in PHP that I'm using to fill in the missing dates after the fact:

function formatResults($inbound, $from, $to) {
    $results = array();
    $count = 0;

    // In order not to lose any results, we have to change how the results are referenced
    $indexes = array();
    $stats = array();
    foreach ($inbound as $stat) {
        // ['listindex'] is the date, renamed in the query
        $stats[$stat['listindex']] = $stat;
    }

    // In a function in case you want to pop it out
    function dateArray($from, $to) {
        $begin = new DateTime($from);
        $end = new DateTime($to);
        $interval = DateInterval::createFromDateString('1 day');
        $days = new DatePeriod($begin, $interval, $end);
        $baseArray = array();
        foreach ($days as $day) {
            $dateKey = $day->format("Y-m-d");
            $baseArray[] = $dateKey;
        }
        $baseArray[] = $to;
        return $baseArray;
    }
    $indexes = dateArray($from, $to);

    // Now all the rows we need to return are uniquely identified in $indexes
    // So we traverse $indexes to create our results array, rather than relying on $inbound
    foreach($indexes as $index) if ($index != '') {
        $data = array();
        // Make sure we do not run into any 'missing index' problems
        if (!isset($stats[$index]))
            $stats[$index] = array(
                'listindex' => $index,
                // ... populate full list of empty fields
            );
        foreach ($stats[$index] as $key => $value) {
            $data[] = $value;
        }
        $results[$count] = $data;
        $count++;
    }
    return $results;
}

MySQL - select missing dates from table?

1 Answers1