Predefined range of date to fill [duplicate]

I want to count the number of item by date, but sometimes the table doesn't contains rows with some dates, and I have a hole in the result, like :

+------------+--------+
| date | number |
+------------+--------+
| 2021-03-12 | 1 |
| 2021-03-15 | 1 |
| 2021-03-17 | 1 |
| 2021-03-18 | 1 |
+------------+--------+


I would like this result:

+------------+--------+
| date | number |
+------------+--------+
| 2021-03-12 | 1 |
| 2021-03-13 | 0 |
| 2021-03-14 | 0 |
| 2021-03-15 | 1 |
| 2021-03-16 | 0 |
| 2021-03-17 | 1 |
| 2021-03-18 | 1 |
+------------+--------+


So, I created a temporary table with WITH, it works:

WITH recursive Date_Ranges AS (
select DATE(NOW() - INTERVAL 3 MONTH) AS Date
union
select Date + interval 1 day
from Date_Ranges
where Date < DATE(NOW())
)
SELECT *, 0 AS number FROM Date_Ranges


Next, I want to retrieve the statistics, I tried with this query:

WITH recursive Date_Ranges AS (
select DATE(NOW()- INTERVAL 3 MONTH) AS Date
union
select Date + interval 1 day
from Date_Ranges
where Date < DATE(NOW())
)
select *, '0' AS value from Date_Ranges
UNION DISTINCT
SELECT DATE(time) AS date, count(*) AS number
FROM my_table
WHERE time >= NOW()- INTERVAL 3 MONTH
AND time <= DATE(NOW())
GROUP BY DATE(ca_time)



But the UNION doesn't work because the value is different so mysql keep the 2 lines:

example:
+------------+--------+
| date | number |
+------------+--------+
| 2021-03-12 | 0 |
| 2021-03-13 | 0 |
| 2021-03-14 | 0 |
| 2021-03-15 | 0 |
| 2021-03-16 | 0 |
| 2021-03-17 | 0 |
| 2021-03-18 | 0 |
| 2021-03-12 | 2 |
| 2021-03-13 | 6 |
| 2021-03-14 | 7 |
| 2021-03-15 | 8 |
| 2021-03-16 | 9 |
| 2021-03-17 | 5 |
| 2021-03-18 | 0 |
+------------+--------+



How the lines with 0 can be merge with the second union ?

Thanks.

Edit:

I tried with this query, but dates without result are not here:

WITH recursive dates AS (
SELECT DATE(NOW() - INTERVAL 3 MONTH) AS Date
UNION ALL
SELECT Date + interval 1 day
FROM dates
WHERE Date < DATE(NOW())
)
SELECT date, COALESCE(COUNT(1), 0) AS number
FROM dates
LEFT JOIN geo
ON dates.date = DATE(geo.ca_time)
WHERE departmentCode = '89'
GROUP BY geo.ca_time
ORDER BY geo.ca_time


result:

+------------+--------+
| date | number |
+------------+--------+
| 2021-02-21 | 1 |
| 2021-02-22 | 11 |
| 2021-02-27 | 8 |
...

Continue reading...