Calculate accurate power consumption (KWh) with missing data in the time series data

Grafana Dashboards
1

Hi All, I’m here with another power consumption calculation question! There are many of them already on the forum, but I have had no luck with my specific requirement.

  • What Grafana version and what operating system are you using?
    V8.1.0 running as a plugin on HomeAssistant.

  • What are you trying to achieve?
    I am trying to calculate power consumption, based on a data set of per-second instantaneous power usage for the circuits in my home.

  • How are you trying to achieve it?
    I have a device that is connected to my home power distribution board. This device measures power usage per second, and sends it to InfluxDB. One measurement per circuit, per second. This measurement is in watts.

I am trying to use this data to calculate power consumption (Kilowatt-hours) so that I can graph that out per day/week/month etc.

I used the following query in order to calculate the consumption, and I’m happy to report that it works as expected! Doing the math by hand matches with what I see on the graphs.

SELECT sum("mean") FROM (
SELECT mean("CH01") FROM "Nanoview_Power_Measurement" WHERE ("type" = 'live_power') AND $timeFilter GROUP BY time(1h) fill(previous)
)
 WHERE $timeFilter GROUP BY time(1h) fill(null)

The inner query is to just clean the data up a bit, as they don’t always come through per-second.

Below I post two screenshots, one of the time-series data, and another of the calculated consumption. The numbers check out.

[EDIT: Can only post 2 screenshots as a new user] So I post the more important, faulty-data screenshots below.

  • What happened?
    The problem comes in when I have nulls in my data set. My power supply at home is not reliable, and as a result the power consumption meter is sometimes switched off. This means Influx is not getting any new time series data for that time. Which is technically correct, because my home power is switched off, and therefore nothing is consuming power.

What is happening though, is that Grafana is somehow assuming that my power consumption remained constant during that outage, in stead of dropping to 0. The below screenshots explain the situation better.

Consider the time between 12h15 and 14h00 - a period where I was without power. On the consumption graph, Grafana is assuming the period between 13h00-14h00 saw constant power usage, which is definitely not the case.

Time series:

time-series-with-nulls
time-series-with-nulls1517×875 55.1 KB

Calculated consumption

consumption with nulls
consumption with nulls1500×808 50.4 KB

  • What did you expect to happen?
    I would expect Grafana would treat time series data where there are no data / nulls as 0, and not “backfill” missing data with interpolated data.

  • Can you copy/paste the configuration(s) that you are having problems with?
    See the query that I pasted above, and in my screenshots.

  • Did you follow any online instructions? If so, what is the URL?
    I used this thread as inspiration: Calculate Energy Consumsion manually - Grafana - Grafana Labs Community Forums

2

Are you saying the above is happening in this below graph?
image

3

Yes, exactly!

This graph plots the power usage of my water heater. When the power goes out then the water heater is not using any power. But since the tool that I use to measure the power also goes out, then I’m stuck with no data during that time, instead of 0W

1 Like