How to Fill Missing Dates in MySQL Date Ranges?

Fill missing dates in date range in MySQL

In MySQL, handling missing dates requires determining the required date and filling the gap with the appropriate value. Fortunately, there is a solution, and it involves utilizing the NUMBERS table trick to generate an ascending list of dates.

First, create a NUMBERS table containing an auto-incrementing id column:

<code class="language-sql">CREATE TABLE numbers (
  id int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>

Use an INSERT statement to populate the table with the required number of values.

Next, use DATE_ADD to generate a date list based on the id value:

<code class="language-sql">SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY)
FROM `numbers` `n`
WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14'</code>

Replace "2010-06-06" and "2010-06-14" with your own start and end dates respectively.

Finally, perform LEFT JOIN based on the time part and the data table:

<code class="language-sql">SELECT `x`.`ts` AS `timestamp`,
          COALESCE(`y`.`score`, 0) AS `cnt`
     FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%Y-%m-%d') AS `ts`
             FROM `numbers` `n`
            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = STR_TO_DATE(`x`.`ts`, '%Y-%m-%d')</code>

In this query, missing dates will be filled with 0. To keep the original date format, use DATE_FORMAT(x.ts, '%d.%m.%Y') AS timestamp. Note that the y table has been modified here to ensure that the date format is consistent and the STR_TO_DATE function is used for conversion. This is more reliable than the method in the original article.

The above is the detailed content of How to Fill Missing Dates in MySQL Date Ranges?. For more information, please follow other related articles on the PHP Chinese website!