How to Fill Missing Dates in a MySQL Date Range?

Fill missing dates in date range in MySQL

In some cases, you may encounter tables with missing dates within a date range. This may disrupt the data analysis and plotting process. To bridge this gap, MySQL provides a solution to fill in missing dates with specified numeric values.

Let us consider the example of a table that records scores associated with a specific date. If some dates are omitted, our goal is to complete them with placeholder values ​​(e.g. 0) to get a contiguous range.

Meeting challenges

MySQL lacks native recursive functionality, but we can use the NUMBERS table trick to generate date sequences based on auto-incrementing columns.

Generate date sequence

1. Create NUMBERS table:

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

2. Fill table:

<code class="language-sql">INSERT INTO `numbers`
  ( `id` )
VALUES
  ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围</code>

This will generate as many rows as the date range requires. (Note: The number of inserted rows needs to be adjusted according to the date range. Simply inserting NULL cannot automatically generate enough rows)

3. Construct date sequence:

<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 the dates "2010-06-06" and "2010-06-14" with your desired start and end dates in YYYY-MM-DD format.

Fill in missing dates

1. Create LEFT JOIN:

<code class="language-sql">SELECT `x`.`timestamp` 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 `timestamp`
             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`, '%Y-%m-%d') = `x`.`timestamp`;</code>

Replace y with your actual data table and adjust the date format accordingly. (It is recommended to use a unified YYYY-MM-DD format to avoid problems caused by format conversion)

By executing this query, you get the complete set of dates in the specified range, where missing values ​​are filled with the specified 0 value. (Note: numbersThe table needs to be pre-populated with enough rows to ensure that the entire date range is covered.)

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