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)

How 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       |

@TimBiegeleisen, oops sorry my sample is wrong ... let me fix it — Aaron, Jan 10 '20 at 08:23
Consider handling issues of data display in application code — Strawberry, Jan 10 '20 at 08:26
Unrelated to solving this problem (which I think @Strawberry has nailed), but do you really mean to be grouping by a timestamp that includes seconds? You are showing only 9 records in a 6 day period, is it even remotely likely that you'd get two on the same second? — Greg Schmidt, Jan 10 '20 at 08:29
@GregSchmidt, actually the timestamps is doesn't matter .. it's ok if it removed ... — Aaron, Jan 10 '20 at 08:31
I found a discussion that similiar with my case, but I am confused to implement it .. https://dba.stackexchange.com/questions/196905/how-to-display-all-the-dates-in-the-date-range-having-group-by-dates — Aaron, Jan 10 '20 at 08:32

score 1 · Answer 1 · answered Jan 10 '20 at 08:33

We can try introducing the missing dates via a union with a calendar table:

SELECT tanggal, COUNT(id) AS jumlah
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 dates.tanggal, 0
FROM
(
    SELECT '2020-01-02' AS tanggal UNION ALL
    SELECT '2020-01-03' UNION ALL
    SELECT '2020-01-04' UNION ALL
    SELECT '2020-01-05' UNION ALL
    SELECT '2020-01-06' UNION ALL
    SELECT '2020-01-07'
) AS dates
LEFT JOIN tb_trafik_berita ttb
    ON dates.tanggal = DATE(ttb.tanggal)
WHERE ttb.tanggal IS NULL
ORDER BY
    tanggal;

FanoFN · Accepted Answer · 2020-01-13T01:00:08.133

Left join this whole month query with your original query (change to a sub-query) like this:

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 (tanggal BETWEEN '2020-01-02 00:00:00' AND '2020-01-07 23:59:00') 
GROUP BY tanggal) YY 
ON XX.dm=DATE(YY.tanggal)
ORDER BY XX.dm;

Edit:

If you want to specify until which date, you can add a WHERE clause in the outer query and you can remove the BETWEEN in the second sub-query (your original query) like following:

#add WHERE clause here
WHERE XX.dm BETWEEN 2020-01-02' AND '2020-01-07'

Edit 2: The above query only generate dates within the current month you assign it to. If you want to have overlapping dates, I suggest you create a calendar table. The following is using example from link : https://stackoverflow.com/a/2157776/10910692 (that generate 10,000 rows of backward dates from CURDATE() interval.) . Here I use the same query to generate 20,000 combination of days with current date as the center.

CREATE TABLE calendar (
dates DATE);

INSERT INTO calendar
SELECT dm 
FROM (
    SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS dm FROM 
    (SELECT 0 AS a 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) AS a
    CROSS JOIN 
    (SELECT 0 AS a 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) AS b
    CROSS JOIN 
    (SELECT 0 AS a 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) AS c
    CROSS JOIN 
    (SELECT 0 AS a 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) AS d
) a UNION ALL
SELECT dm 
FROM (
    SELECT CURDATE() + INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY AS dm FROM 
    (SELECT 0 AS a 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) AS a
    CROSS JOIN 
    (SELECT 0 AS a 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) AS b
    CROSS JOIN 
    (SELECT 0 AS a 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) AS c
    CROSS JOIN 
    (SELECT 0 AS a 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) AS d
) a;

The query above will insert into calendar table with date range from 1992-08-28 to 2047-05-30. Then you only need to LEFT JOIN the calendar table with the rest of your code like following:

SELECT XX.dates AS 'Tanggal',IFNULL(YY.jumlah,0) AS 'Jumlah' 
FROM calendar XX
LEFT JOIN
(SELECT COUNT(id) AS jumlah,tanggal 
FROM tb_trafik_berita 
GROUP BY tanggal) YY 
ON XX.dates =DATE(YY.tanggal)
WHERE XX.dates BETWEEN '2019-12-13' AND '2020-01-07' 
ORDER BY XX.dates ;

why ```BETWEEN``` is doesn't work ? and the output showing date 01 to 31 ... it should showing date 02 to 07 only ... — Aaron, Jan 10 '20 at 14:28
The first sub-query generate dates for one whole month and since it's `LEFT JOIN` it will show all dates in that current month running. If you want to show specific date or maybe limit to just today date (dynamically) you can add a `WHERE` clause. You can check my updated answer. — FanoFN, Jan 11 '20 at 00:35
I found bug here, if i put `WHERE (XX.dm BETWEEN '2019-12-13' AND '2020-01-11' )` filter date range in different year it showing like this https://i.postimg.cc/DZGtq4gn/xxxxx.png, begin from 01 at 2020 ... — Aaron, Jan 11 '20 at 15:14
The first sub-query only generates the whole month dates of current month. If you want to do overlapping between month and/or year, you need to create a bigger calendar. Take this answer example https://stackoverflow.com/a/2157776/10910692 — FanoFN, Jan 13 '20 at 00:37
I think if you're always going to use the calendar as reference, you better have a calendar table prepared so that you don't need to write long queries like above. I'll post a working `CREATE TABLE calendar` for you shortly. — FanoFN, Jan 13 '20 at 00:50

MySQL date range if data not available display 0

2 Answers2