MySQL get number of records in each day between two given dates

Question

I'm writing this query where it gets a row value and it will return the number of records for each day for that row between two given dates and returns 0 if there is no records for that day.

I've written a query which does this for the past week.

Current Query:

select d.day, count(e.event) as count
                      from ( 
                            select 0 day union all 
                            select 1 union all 
                            select 2 union all 
                            select 3 union all 
                            select 4 union all
                            select 5 union all 
                            select 6 
                           ) d 
                      left join event e
                           on e.timestamp >= current_date - interval d.day day
                           and e.timestamp < current_date - interval (d.day - 1) day 
                           and e.event = ?
                      group by d.day

The problem is this returns only the results for a fixed number of days.. I want to be able to give it two dates (start and end dates) and get the record counts for each day where I don't know the number of dates in between.

Answer 1

You could use/create a bona-fide calendar table. Something like this:

SELECT
    d.day,
    COUNT(e.timestamp) AS cnt
FROM
(
    SELECT '2020-01-01' AS day UNION ALL
    SELECT '2020-01-02' UNION ALL
    ...
    SELECT '2020-12-31'
) d
LEFT JOIN event e
    ON e.timestamp >= d.day AND e.timestamp < DATE_ADD(d.day, INTERVAL 1 DAY)
WHERE
    d.day BETWEEN <start_date> AND <end_date>
GROUP BY
    d.day;

I have covered only the calendar year 2020, but you may extend to cover whatever range you want.