Time Series Plot Issues: Do not show the zero change

Grafana Time Series Panel
1

Hi, in my DB, there is only 2 records, one on 04/06, one on 04/19, which you can see here:

image
image1200×271 7.87 KB

However, instead showing 2 points, there is a line between these two dates.
I want to know if there is a way to show 0 if there is no record on that date?

I am new to SQL as well and here is my query:

select distinct date(CRTD_TS) as TIME, count(ID) as ‘SUCCESS’
from RECORD_TABLE
where STATUS=1 and ACT_TYPE=1and $__timeFilter(CRTD_TS)
group by TIME
order By tTIME asc;

(Here STATUS and ACT_TYPE are two filters, and CRTD_TS means created time stamp)

Thank you for the help.

2

Maybe this?

image
image691×226 13.2 KB

3

I tried null or null as zero, but it was still a line. Thank you for the help.

4

Try this, should fill in the gap(s)

select distinct $__timeGroup(CRTD_TS,'24h',0) as time, 
       'SUCCESS' metric,
	   count(1) as value
from RECORD_TABLE
where STATUS=1 and ACT_TYPE=1and $__timeFilter(CRTD_TS)
group by $__timeGroup(CRTD_TS,'24h',0)
order By 1 asc;

image
image986×344 51 KB

5

That works well! Thank you Yosi.

1 Like