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) <p>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:</p><pre class="brush:php;toolbar:false">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 <p>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.</p>

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!