How to display 0 when there is no data in time series?

Grafana Time Series Panel
1

1
11877×781 81.7 KB

As you see, there is no data from 02/12 to 02/18, but I want to display 0’s on them.
I have set “null value” to “null as 0”
Can anyone help me to deal with the problem?

the query code:
SELECT STR_TO_DATE(DATE_FORMAT(ap.created_on, ‘%Y-%m-%d’), ‘%Y-%m-%d’) AS time, count(distinct account_id) AS ‘Registrations’
FROM account as a inner join account_profile as ap on a.email_address = ap.email_address
WHERE ap.created_on < curdate() AND ap.created_on >= DATE_SUB(curdate(), INTERVAL 14 day) and ap.is_guest=“AA==”
group by DATE_FORMAT(ap.created_on, ‘%Y-%m-%d’)
;

1 Like
2

Use $__timeGroup Grafana macro with fill parameter, see doc:

$__timeGroup(dateColumn,'5m', 0) Same as above but with a fill parameter so missing points in that series will be added by grafana and 0 will be used as value.

3

Thank you so much! Almost done!
Now there’s another problem: there’s more data that you don’t need.

1
11491×713 60.2 KB

I don’t need data beyond seven days, which is the part circled by red pen. I tried to put the part after group by directly into $__timeGroup, but it didn’t work. I hope you can help me.

4

Hello chaoyuemichaelxi,

I know its been a year since your issue, but do you by any chance still know how and where exactly you insertet the $__timeGroup macro?

I want to do the exact same, displaying no value as 0 but for some reason, I can’t make it work.