MySQL date range if data not available display 0

Question

I've a table here here is the structure : id,id_berita,ip_user,tanggal

I try to use this query :

select count(id) as jumlah,tanggal from tb_trafik_berita WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') group by tanggal

and the output of that query,like this :

tanggal | jumlah |
____________________|_________|
2020年01月02日 04:13:25 | 1 |
2020年01月02日 04:13:39 | 1 | 
2020年01月02日 05:02:44 | 1 |
2020年01月03日 13:23:22 | 1 |
2020年01月03日 13:29:20 | 1 |
2020年01月04日 01:36:45 | 1 |
2020年01月04日 01:41:30 | 1 |
2020年01月07日 14:37:08 | 1 |
2020年01月07日 15:05:21 | 1 |

the output only showing data that available on table (only these date : 02,03,04,07)

if I want the output also showing date that not available on table : 02,03,04,05,06,07 , but put value 0,

so the expected output is :

tanggal | jumlah |
____________________|_________|
2020年01月02日 04:13:25 | 1 |
2020年01月02日 04:13:39 | 1 | 
2020年01月02日 05:02:44 | 1 |
2020年01月03日 13:23:22 | 1 |
2020年01月03日 13:29:20 | 1 |
2020年01月04日 01:36:45 | 1 |
2020年01月04日 01:41:30 | 1 |
2020年01月05日 00:00:00 | 0 | <<< set 0 cuz data on table not available
2020年01月06日 00:00:00 | 0 | <<< set 0 cuz data on table not available
2020年01月07日 14:37:08 | 1 |
2020年01月07日 15:05:21 | 1 |

I've try this query :

SELECT XX.dm AS 'Tanggal',IFNULL(YY.jumlah,0) AS 'Jumlah' FROM
(SELECT dm FROM
(SELECT CONCAT_WS('-',df,CONCAT(b,c)) dm FROM 
(SELECT DATE_FORMAT(CURDATE(), '%Y-%m') df) a, 
(SELECT 0 b UNION SELECT 1 b UNION SELECT 2 UNION SELECT 3) b,
(SELECT 0 c UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9) c ) d WHERE DAY(dm) BETWEEN 1 AND DAY(LAST_DAY(dm))) XX
LEFT JOIN
(SELECT COUNT(id) AS jumlah,tanggal 
FROM tb_trafik_berita where id_berita = '15'
GROUP BY tanggal) YY 
ON XX.dm=DATE(YY.tanggal)
#add WHERE clause here
WHERE XX.dm BETWEEN 2020年01月02日' AND '2020-01-07' 
ORDER BY XX.dm;

but it's still has bug if i put WHERE (XX.dm BETWEEN '2019-12-13' AND '2020-01-11' ) filter date range in different year it showing like this :

enter image description here

the bug is begin from date 01 at 2020 only...

Anyone can help me to fix the bug ?

Answer 1

You should use a RECURSIVE CTE available in Mysql8.0 and above.

WITH RECURSIVE abc AS (
 select CAST('2020-01-01' as DATE) as a 
 union all 
 select ADDDATE(a,interval 1 day) 
 from abc where a<'2020-01-31') 
SELECT * FROM abc;

Above will output all dates between '2020-01-01' and '2020-01-31'.

If you join this to your query, you should see 0 for dates that have no value in your table.

WITH RECURSIVE abc AS (
 select CAST('2020-01-01' as DATE) as a 
 union all 
 select ADDDATE(a,interval 1 day) 
 from abc where a<'2020-01-31')
select SUM(x.jumlah), x.tanggal from (
select 
 count(id) as jumlah,
 tanggal 
from tb_trafik_berita 
WHERE (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') 
group by tanggal
union all 
select 0,a from abc
) x 
GROUP BY x.tanggal