How to Populate Missing Dates in MySQL Data Retrievals?

Retrieving Data Between Dates: Populating Empty Records

In MySQL, selecting data within a specified date range is commonly achieved using the BETWEEN operator. However, this approach may result in missing dates where no data is present. To resolve this, a technique involving 'calendar tables' is employed.

The concept of a calendar table is to create a table that contains a sequence of dates within the desired range. This is achieved using the following code:

CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

This table provides a range of numbers from 0 to 9. To generate the calendar table, we join ints with itself multiple times to create a list of date intervals:

SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
FROM ints a JOIN ints b
ORDER BY a.i * 10 + b.i

The result is a table with a sequence of dates from '2009-06-25' to '2009-07-04'. To retrieve the data, we left join the calendar table with the original table:

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
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

This query returns the data for all dates between '2009-06-25' and '2009-07-01', including rows with no data that are populated with '0'.

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