Single day delta (midnight to midnight)

Grafana Support
#1

I am asking for help here.

I just started with Grafana and although I am familiar with SQL syntax (data is stored in Influxdb), I am failing to work this one out. I have a device that sends once in a while status of energy consuption. Not what was consumed but what is on the display. It could at 15:05 - 151040 kWh, then at 17:03 - 151043 kWh etc … The device does not care about the time so there is never any data received at midnight. I however need to monitor daily electricity usage. There are 2 ways to achieve what I am after so hopefully someone can point to the right one:

The idea is to:

  • get the yeasterday’s last value
  • get the today’s first value
  • count the amount of minutes between both measurements
  • count how far we are from the yesterday’s and today’s values compared to midnight
  • output a single value that would represent the midnight value

There are 2 ways:

  1. grafana way
    So far I was able to get the today’s first value but cannot come up with a way to get the yesterday’s value. In other words, is there any query to count that at all?

  2. influxdb way
    I can grab both (first and last) values from influxdb, get their times, count it in shell and then insert a new value into influxdb with a special tag to be able to differentiate that it was counted (this is actually not a big deal to achieve but it requires some extra coding, working with cron and so is vulnerable to any issues)

I was trying to make it work the grafana way but it seems bnnot feasible. Could someone please think of a way to achieve this not adding extra data into DB?

#2

Since this did not seem to be feasible, I was able to write my own script that does the calculations and simply inserts the values into InfluxDB.

It calculates the 00:00:00 value (to be the day’s first value) and 23:59:59 value (to be the last value). Using then max(“value”) - min(“value”) does the trick and I can quite precisely monitory the energy consumption even though I don’t receive reports at specific times.