Calculate hourly consumption

Hi, I am using Influxdb together with Node Red and Grafana for presentation of power consumption. Works fine. Now I want to take the next step and present the energy consumption. Via Node Red I get continous readings from my energy meter hour by hour. It is injected in Influx in this format, 2020-04-16T19:00:09.347836627Z 2020 4 16 20:0:0 16356.52 2020-04-16T20:00:08.739825072Z 2020 4 16 21:0:0 16358.15 How do I get the hourly consumption the most rational way?

How often do you get an update from your energy meter, you mention hour by hour??

Yes, correct. Once every hour.

If you only get a reading once an hour, how can you chart power?
Power is the rate which electrical energy is transferred by an electric circuit at any moment in time, so I'm not sure how your feed is structured if you only get a hourly update.
Do you know what the format of the feed is?

The meter gives power every third second, current and voltage every tenth second and energy once every hour. This energy figure is the statement that later sum up to the invoice. My idea is, obviously, to take the second latest energy figure and subtract it to the latest everytime a new energyreading is comming in to the system.

Is there a clever Node doing this task perhaps? Or is there any other way to do this?

Well you could either calculate energy within node-RED from your incoming power readings using node-red-contrib-watt2kwh
Or use context storage to save the previous value, and help do the math and calculate energy accumulations.

1 Like

Will you be adding anything to the database that cannot be calculated later from the data already in the database?

I have problem with getting the second last value from the database. I have tried

SELECT energyread FROM "kwh" WHERE time = now() - 50m AND now() - 70m

in an Influxdb-In node just to get the second latest value, but that query is not working.

Or could I reduce the timestamp to minutes and then query: SELECT energyread FROM "kwh" WHERE time = now() - 60m...???

How would you do?

SELECT sum(energyread) FROM "kwh" WHERE time > (now() - 60m)

Will give the total from the last 60 minutes.

I just tested and that gave back the latest reading... I need the second latest...

Is it possible to give a time window from now()? Like (now() - 50m to 70m) ?

I have done some testing but the result is just empty answers....

I prefer to do the calculations in node-RED, and calculate the day's energy from the power feed itself.
As my power feed updates every 5 seconds, the daily energy data is also updated every 5 seconds.
That data can then be displayed as text (as per gif below), or fed into influx and presented in Grafana as a graph or bar chart.


However there are lots of ways to achieve similar results.

1 Like

That looks nice!!!!

To do the calculations in Node Red means that you for example use functions like Context? Are you getting energy in consumption from the meter or do you just get a meter stand like I do?

In a few weeks we will get solar panels installed, I hope to set up some kind of presentation (like yours) for our own production...

Yes. I firstly convert the power reading (watts) into energy units (joules), then using context create a running total, which is set to zero at midnight.
It's easily done with node-red-contrib-watt2kwh, but not hard to code in a function node.

I built a device probably 5 years ago, that uses Current Transformer clamps on the power feeds (non-invasive) to measure usage, solar generation, diversion & mains voltage.
The device uses a ATmega328p processor, runs constantly and feeds data to my Pi via RF.

I have created my dashboards in node-RED, and then imported the Grafana charts into it, so I never have to touch Grafana as all the queries are controlled from within node-RED. See Contextmenu location - #45 by Paul-Reed

1 Like

What gauges are those? Are they in a NR dash?

They are @hotNipi's node-red-contrib-ui-artless-gauge's in a NR dashboard.
I really like them, simple modern styling.

Boom moment - exactly what I have been searching for - a multi sector gauge. :grinning: :grinning:.

SELECT sum(energyread) FROM "kwh" WHERE time > now() - 70m AND time < now()-50m should do the trick.

Btw, Nice application to test your Influx query's

Thank you edje11! That worked!!! Now I can compare the latest and the second latsest value. Great work! Thanks alot everyone!!!

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.