I’m trying to get grafana to show 0 when there is missing data every minute.
My data is drawn from a database, where each row has a timestamp, and number of events in that minute. Because the devices logging the data are very low power, they do not send data if the number of motion events is 0, however, no matter what I try, I cannot get grafana to fill in the gaps.
The image above shows points longer than a minute apart being joined up, however this is not what I would like. I want Grafana to insert 0 values instead. The data looks like this:
and the query I used to get it is:
SELECT lora_timestamp, payload FROM events WHERE device = '9c8a6e13-48f0-454c-8910-9f5eba2026d2' LIMIT 50
I have tried using the
$__timeGroup(lora_timestamp,'1m', 0) function, but it just return the exact same data. I got this from: https://community.grafana.com/t/how-to-display-0-when-there-is-no-data-in-time-series/43670
How is Grafana supposed to know the rate at which you wish you were sampling
If you want zeroes in your data, you should put them there.
Grafana is a visualisation tool and will display the data it gets from the
queries it’s told to perform. If it doesn’t get data, it won’t display it.
Create a date time table with 1 minute increments and left join to your events table. Where is there is no join make the value of payload 0 using a case statememt.
You will always run into one issue because your time interval is not exactly 1 minute sometimes it is 1 minute and 1 seconds
Thanks for the reply. The data is always 1 minute spaced realtive to each other, it just might be a couple seconds ahead relative to the clock depending on when the sensor was turned on. The issue I see with manually adding time rows is that it would end up using up space on the database, and I wanted to see if the was a more programatic method available. If it makes it easier, I can tweak the backend to sync the timestamp to be on the minute.
It would be very minimal and you can even do it dynamically.
But that is outside of grafana. What is your data source
its a supabase PostgreSQL instance
I think I can get away with not using line graph and a bar chart style graph instead, as it represents the data how I want, although I now have another issue I have opened another thread for, to get dynamic panel names.