How to Populate Missing Dates in MySQL Queries Between Two Dates?

Populating Missing Dates When Retrieving Data Between Two Dates in MySQL

In MySQL, when selecting data between two dates using the BETWEEN operator, it's possible that some dates within the specified range may not have any corresponding data. This can result in empty or missing rows in the query results.

Problem Statement:

Consider a table named "tbl" with data associated with dates, as shown below:

If we want to retrieve all data between '2009-06-25' and '2009-07-01' using the following query:

SELECT data FROM tbl WHERE date BETWEEN '2009-06-25' AND '2009-07-01';

The query would only return rows with existing data within the specified range. This means that dates without any data, such as '2009-06-26', '2009-06-27', and '2009-06-28', would be omitted from the results.

Solution: Calendar Tables for Populating Missing Dates

To address this issue and include all dates within the specified range, regardless of whether there is data for them, we can utilize a concept known as "calendar tables." These tables help generate a sequence of dates within a given interval, essentially serving as a "pseudo" table of all possible dates.

Here's an example of how to create a calendar table in MySQL:

-- Create a table with integers from 0 to 9
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- Generate a sequence of dates from '2009-06-25' to '2009-07-01'
SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY AS date
FROM ints a JOIN ints b
WHERE date BETWEEN '2009-06-25' AND '2009-07-01';

Once you have created the calendar table, you can perform a left join with your data table to populate the missing dates. Here's the updated query:

SELECT cal.date, tbl.data
FROM (
SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY AS date
FROM ints a JOIN ints b
WHERE date BETWEEN '2009-06-25' AND '2009-07-01'
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

This query retrieves all dates within the specified range, including those without any data in the "tbl" table. Dates with missing data will have a NULL value in the "data" column.

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