How to Fill Missing Dates in a MySQL Range with Zero Values?

MySQL date range fills missing values

Suppose you have a table with two columns (date and score) and you want to fill in missing dates to get a continuous sequence of values. For example, if your table currently contains the following data:

<code>日期      分数
-----------------
2010-08-01  19
2010-08-02  21
2010-08-04  14
2010-08-07  10
2010-08-10  14</code>

You want to fill the missing dates (2010-08-03, 2010-08-05 and 2010-08-06) with 0s to get the following result:

<code>日期      分数
-----------------
2010-08-01  19
2010-08-02  21
2010-08-03  0
2010-08-04  14
2010-08-05  0
2010-08-06  0
2010-08-07  10
2010-08-10  14</code>

To achieve this you can use the following query:

<code class="language-sql">SELECT x.ts AS timestamp,
       COALESCE(y.score, 0) AS cnt
FROM (SELECT DATE_ADD('2010-06-06', INTERVAL n.id - 1 DAY) AS ts
        FROM numbers n
       WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 DAY) < '2010-08-11') x
LEFT JOIN your_table y ON DATE(x.ts) = DATE(y.date);</code>

Here’s a breakdown of the query:

• Create a table of numbers (numbers) with auto-incrementing values.
• Use DATE_ADD to construct a list of dates from a specified starting date ('2010-06-06' in the example) and increment the number of days based on the id of the number in the numbers table.
• Use LEFT JOIN to merge the list of dates with your original table y based on the date part.
• Use COALESCE to replace any null values ​​in y.score with 0.
• You can use DATE_FORMAT to change the date format if needed.

Please replace your_table with your actual table name. Make sure you have a table named numbers that contains an auto-increment column named id that contains consecutive integers starting at 1. If not, you need to create one first. For example: CREATE TABLE numbers (id INT AUTO_INCREMENT PRIMARY KEY); Then insert a sufficient number of records to cover your date range.

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