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