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


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’)
;

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.

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


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.