Insert '0' when no data point is available on timeseries charts

I am charting a timeseries data source, but there are some points missing for two reasons:

  • A particular day/time has no entry
  • The particular statistic has stopped reporting (i.e. because the load on it has stopped)

Is there a way to automatically populate with zeroes for the whole timerange that is selected on the dashboard/chart? In one case I’ve messed around with creating a temporary table of dates and filling them in, but that forces it to a particular date range and doesn’t apply the time-series range of the dashboard

See this chart image to get an idea of what I’m referring to:

You can see that between ~03/07 and ~03/09 there are no points, and then after ~03/14 (when the load stopped) there are no points. I would like those to be filled in with 0’s.


are you not able to intercept the null problem in the query?

1 Like

Thanks for your response! It’s hard, but not impossible, to intercept null values because I need to know what the interval is (day, month, etc) and generate a temporary table with that date range filled in with 0’s, and then join it to the actual data series. I’ve done it in a few places, but the blocker on that is that it no longer acts like a ‘time-series’ chart because the range isn’t dynamic.

Any obvious things I’m missing on that or any other creative suggestions?

Which DB are you using?

isnull(boombam,0) or coalesce(boombam,0)

The problem with isnull or coalesce is that it requires there to be a datapoint there to fill in…which my primary data source doesn’t have.

For instance, my table might looks like this:

date value
2023-01-01 1
2023-01-02 2
2023-01-05 10

If I do select date, value from <table> and chart it, I end up with a gap between 2023-01-02 and 2023-01-05. There’s no way to fill in 0 for 2023-01-03 and 2023-01-04 within that statement…those rows don’t exist in the table, and therefore there is nothing to evaluate for ‘isnull’ or ‘coalesce’.

What I have done is to create a temp table:

date value
2023-01-01 0
2023-01-02 0
2023-01-03 0
2023-01-04 0
2023-01-05 0

And join it with my original table, in which case I do get all the points filled in. That’s fine in one case, but it doesn’t allow me to change the timerange on the dashboard itself and re-draw the graph.

gotcha. So you need a calendar table to do a left join to. outside of scope of grafana though

but here is something for you,

Thanks, that’s helpful, but still doesn’t address my challenges with Grafana and it’s time-series ranges. Mainly I was hoping for a feature within Grafana itself but I understand that’s not possible

You want grafana to fill in the gaps of data that does not exist in your table? definitely a feature I agree.

I asked you which DB you are using and I didn’t get answer. For example I work with PostgreSQL: PostgreSQL data source | Grafana documentation
And there is:

macro `$__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.

So there is already “Insert ‘0’ when no data point is available on timeseries” feature (it is just clever SQL), for this particular DB. Please provide details about used DB.

Apologies. I’m using BigQuery, I’ll have a look for an equivalent macro. Thank you!

So it looks like macros are indeed supported, but when I put that one in, I still get gaps in my chart. From reading the docs, it sounds like it’s supposed to do what I’m asking for, but it’s not

OK, please provide the doc link, where it is explicitly mentioned that BigQuery supports that macro as I proved that with PostgreSQL doc. Because I don’t believe.

In here: Google BigQuery plugin for Grafana | Grafana Labs.

And it at least “accepts” that macro, but perhaps it’s not actually working?

No, it is not supported. Check number of parameters:

$__timeGroup(timeColumn,interval) (2 parameters) != $__timeGroup(dateColumn,'5m', 0) (3 parameters)

Doc is your good friend and it is not saying that’s supported. So that “it is not working” is expected.

Got it, thank you.

You may use transformation (and/or other transformation to achieve desired format), e.g. binary + 0: