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.
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?
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.
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
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.
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.
