Graphing energy usage in Grafana

Configuration
1

Hi All,

I am experimenting with InfluxDB and Grafana to get insights in the data generated by my Home Assistant setup. And for now, I seem to get stuck on displaying readings from my smart energy meter.

Although I know this is a grafana-specific question, I am asking here, since I think the use case may be similar to what others have done. And if a solution is found/presented, it may help other HASS users who want to do the same.

My smart reader has two separate meters, one for normal tarriff and one for low tarriff during night hours and in weekends. Those are available in my HASS config thanks to the DSMR component as :
sensor.power_consumption_low
sensor.power_consumption_normal

The readings are always increasing of course, as they show the total amount of energy used over time. What I want to do is display the electricity used over a period of 24 hours for the past X days, as bars (one bar per day), where each bar consists of two stacked colors: one for the low tarriff meter, one for the normal tariff meter, so that stacked together, they show the energy used for that day (or for "today so far).

So, does anybody know how I can turn continually climbing meter readings over time during the day, into a cumulative reading for the whole day?

1 Like
2

Hi @aetjansen

I’m not sure to understand your use case, but I’ll try to share my experience.

I’ve a smart meter that gives the instant reading. I also wanted the cumulative for the day but an integral function was needed and InfluxDB still not implement it.

My knowledge of InfluxDB and Grafana is limited but at last I gave up waiting for the integral function to be implemented.

3

Hi fakezeta,

I figured something out, but not sure if that is exactly what I want, maybe it helps you.

My sensor readings during the day as not at exact intervals, but the value read is the cumulative meter reading since the meter was installed in my house. So, for example (simplified):
Reading 1 at around 0:00 (midnight) for normal tariff meter: 3000
Reading 2 for normal tariff meter: 3001
Reading 3 for normal tariff meter: 3004
etc
Reading 100 (at e.g. 23:59:59): 3010

What and the next day, that sensor value contains to increase. But from it, one can derive the amount of power consumed in a time period. IN my desired case: 1 day. So, my graph would show 10 (3010 - 3000) for that day.

If I query grafana as follows:
FROM default kWh WHERE entity_id = power_consumption_normal
SELECT field(value) mean() difference()
GROUP BY time (1d) fill(null)
FORMAT AS time series

That looks to give me the number I want, but it is somehow offset and gives me the cumulative not from midnight to midnight, but from 1am to 1am…

1 Like
4

Perfect!,
I was searching for a way to graph my daily water usage in grafana, working brilliantly now. Thank you.

5

oh well, in this way it’s ok.

immagine

I have my istant values for Power, Tension, Intensity and Cos φ so I need an integral of all the readings.

6

Hi @fakezeta,
I was facing a similar issue, I have a bunch of smart meters with only instant power reading.

Influxdb (and Grafana) has an integral function: https://docs.influxdata.com/influxdb/v1.4/query_language/functions/#integral

I used it to calculate daily, monthly (well every 30 days) and total cumulative energy use with some continuous queries.

I had issues with Influxdb and HA and the CT meter running together on the Raspberry Pi so it stopped working, but I can dig out the continuous queries if you’re interested.

@aetjansen, maybe the time offset you see is because of timezone setting in Influxdb? I vaguely remember reading something about it a while back when researching how to use Influxdb to work out energy consumption from power readings, sorry I can’t offer more details.

7

Wonderful, I was using an old version of InfluxDB that still did not support the integral function.

8

can you tell how to use it in grafana the integral? Yes wish to use the continuous query.
Thanks!

9

These are a couple of my continuous queries in Influxdb:

CREATE CONTINUOUS QUERY cq_electricity_values_1h ON ha BEGIN SELECT integral(power) / 3600 AS energy_1h, integral(cooker) / 3600 AS cooker_energy_1h, integral(boiler) / 3600 AS boiler_energy_1h, min(power) AS min_power_1h, max(power) AS max_power_1h, max(cooker) AS max_cooker_1h, max(boiler) AS max_boiler_1h, mean(tension) AS mean_tension_1h, stddev(tension) AS stddev_tension_1h, min(tension) AS min_tension_1h, max(tension) AS max_tension_1h INTO ha."30_days".electricity_values_1h FROM ha."25_hours".electricity GROUP BY time(1h) END
CREATE CONTINUOUS QUERY cq_electricity_1d ON ha BEGIN SELECT integral(power) / 3600 AS energy_1d, integral(cooker) / 3600 AS cooker_energy_1d, integral(boiler) / 3600 AS boiler_energy_1d, min(power) AS min_power_1d, max(power) AS max_power_1d, mean(tension) AS mean_tension_1d, min(tension) AS min_tension_1d, max(tension) AS max_tension_1d INTO ha.autogen.electricity_1d FROM ha."25_hours".electricity GROUP BY time(1d) TZ('Europe/Athens') END

They take instant power readings from ‘power’ (whole house), cooker, boiler and calculate their energy use by hour (1st CQ) and by day (2nd CQ) and store them in new values. I do some more, similar CQ for some Broadlink SP3S switches.

You can also calculate the energy integrals directly in Grafana from the instant readings in Influxdb. I don’t do it this way because I do the calculations in Influxdb anyway.

This is my query in Grafana, as shown in Query inspector:

SELECT cumulative_sum("energy_1h") FROM "30_days"."electricity_values_1h" WHERE time >= 1520546400000ms

The time filter is calculated by Grafana, I select ‘Today so far’.
Grafana has the added benefit that you can select values for today so far, this week, this month etc., which I haven’t been able to do in Influxdb directly.
I hope this is clear!

grafanaenergy
grafanaenergy1820×283 45.7 KB

3 Likes
10

Thanks for your answer, I will study it.

My goal is (relatively) simple:

for the main (I have a Efergy Hub) I want to have the instant power (this I have from the component) and the energy consumed in last hour, last day, last week, last month … eventually rolling (rolling day, rolling week, rolling month) and/or from beginning of a period (last hour, from midnight to now, from first of week to now, from first of month to now) and keeping the data stored/visible in some way that it is easy to check.

The same I would like to do for some appliances (I do have Sonoff POW, Neo coolcam zwave, Broadlink SP3, xiaomi zigbee switch) which all give instant power but is very confusing how they calculate the energy, I am sure you know what I mean

11

Yes this is exactly what I want for the energy today

12

you use hassio?

How/where you do those queries, or its done inside grafana (newb here)

13

I do not understand very well. I got this, is it the same?

Cattura
Cattura851×1210 58.9 KB

14

AH now I see query inspector, I get this

xhrStatus:“complete”
request:Object
method:“GET”
url:“api/datasources/proxy/1/query”
params:Object
db:“home_assistant”
q:“SELECT cumulative_sum(mean(“value”)) FROM “W” WHERE (“entity_id” = ‘efergy_802171’) AND time >= now() - 6h GROUP BY time(20s) fill(null);SELECT mean(“value”) FROM “W” WHERE (“entity_id” = ‘neo_coolcam_power_plug_12a_power’) AND time >= now() - 6h GROUP BY time(20s) fill(null);SELECT mean(“value”) FROM “W” WHERE (“entity_id” = ‘neo_coolcam_power_plug_12a_power_2’) AND time >= now() - 6h GROUP BY time(20s) fill(null)”
epoch:“ms”
data:null
precision:“ms”
response:Object
results:Array[3]
0:Object
1:Object
2:Object

15

Hi @anon35356645,
no I don’t use Hassio, I use the HA Docker container on a Ubuntu host. It works pretty well so far.

My Continuous Queries are done in Influxdb, you can read them above. I then use Grafana to graph the results.

It looks like in your Grafana query you sum the mean values from your Efergy monitor, which I guess must be instant power readings. I don’t think that will work, as you need to calculate energy use (in wh or Kwh) from the instant power readings (in w or Kw).

My flow is somewhat complicated, as I read the power into an Influxdb database, calculate energy with the integral function, graph in Grafana and use the Influxdb sensor in HA to read for example ‘Energy consumed yesterday’.
I can go through it step by step if you’re really interested.

For things like lights that have fixed power, I think it can be done directly in HA, though I haven’t tried, but this looks good: Calculate power usage

1 Like
16

Could you go through this step by step Manu? Want to achieve exactly the same thing as you, but have been unsuccessful untill now…

1 Like
17

How do you guys do “Monthly” graphing… ie calculate your monthly values. Influxdb doesn’t have “GROUP BY MONTH”

18

I’m also very interested in this! The “difference” selector helped me get the good values when I want “day to day-bars” but when I try this per week instead it gives me totally wrong values… before I knew how to make grafana queries I used to reset zwave-nodes every new week and just fetch the last value in grafana, that gave me the right numbers, but I also skrew up the counter making grafana less effective… I felt that it was the wrong way to use it.

Now I use difference with a counter that doens get reset, it constantly grows and when I try the query below I get a value like 30kHw but I know that it actually should be like 68kHw… why does it not show the exact numbers?

SELECT difference(mean(“value”)) FROM “kWh” WHERE (“entity_id” = ‘pump_energy_kwh’) AND $timeFilter GROUP BY time(1w) fill(null)

19

For those wanting a simpler solution: in 0.87 you can use the utility sensor to achieve daily and hourly kWh readings and feed those into influxdb

2 Likes
20

Yep, and I did a little write-up for those using the DSMR component (or any other way that results in separate sensors for each power tariff) and will add that to the component’s docs as well tomorrow…