How to Retrieve All Dates within a Range, Including Those with Zero Records in MySQL?

MySQL: Retrieve All Dates in a Range, Including Empty Dates

In database management, visualizing userbase growth often requires a graphical representation. To create such a graph, you may have a query that collects daily user counts within a specific date range. However, if some days lack user activity, those dates are omitted from the results. To address this issue, let's explore how to obtain a comprehensive date range, including dates with zero records.

In MySQL, the following query retrieves user counts grouped by day within a specified date range:

SELECT DATE(datecreated), count(*) AS number
FROM users
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated)
ORDER BY datecreated ASC

While this query provides most of the desired information, it excludes dates with zero users. To include empty dates, we can leverage the following approach:

SELECT  *
FROM (
    SELECT date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date
    FROM
    (SELECT 0 AS num
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9) n1,
    (SELECT 0 AS num
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9) n2,
    (SELECT 0 AS num
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9) n3,
    (SELECT 0 AS num
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9) n4,
    (SELECT 0 AS num
        UNION ALL SELECT 1
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
        UNION ALL SELECT 6
        UNION ALL SELECT 7
        UNION ALL SELECT 8
        UNION ALL SELECT 9) n5
) a
WHERE date > '2011-01-02 00:00:00.000' AND date < NOW()
ORDER BY date

With this query, you can generate a sequence of dates within a specified range and include them in the results, even if those dates have no corresponding records in your database.

The above is the detailed content of How to Retrieve All Dates within a Range, Including Those with Zero Records in MySQL?. For more information, please follow other related articles on the PHP Chinese website!