# Calculate hourly consumption
**URL:** https://discourse.nodered.org/t/calculate-hourly-consumption/25035
**Category:** General
**Created:** [17 April 2020 21:14 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035 "2020-04-17T21:14:44Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [17 April 2020 21:14 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/1 "2020-04-17T21:14:44Z")
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?
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [17 April 2020 21:24 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/2 "2020-04-17T21:24:27Z")
How often do you get an update from your energy meter, you mention hour by hour??
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [17 April 2020 21:55 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/3 "2020-04-17T21:55:29Z")
Yes, correct. Once every hour.
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [17 April 2020 22:37 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/4 "2020-04-17T22:37:01Z")
> [@Mrfox](#):
>
> for presentation of power consumption. Works fine.
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?
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [17 April 2020 23:19 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/5 "2020-04-17T23:19:22Z")
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?
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [17 April 2020 23:33 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/6 "2020-04-17T23:33:01Z")
Well you could either calculate energy within node-RED from your incoming power readings using `node-red-contrib-watt2kwh`
Or use [context storage](https://nodered.org/docs/user-guide/context) to save the previous value, and help do the math and calculate energy accumulations.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [18 April 2020 08:00 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/7 "2020-04-18T08:00:28Z")
Will you be adding anything to the database that cannot be calculated later from the data already in the database?
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [18 April 2020 09:11 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/8 "2020-04-18T09:11:36Z")
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?
---
### Author:  [@edje11](https://discourse.nodered.org/u/edje11)
#### Post date: [18 April 2020 13:04 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/9 "2020-04-18T13:04:01Z")
SELECT sum(energyread) FROM "kwh" WHERE time \> (now() - 60m)
Will give the total from the last 60 minutes.
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [18 April 2020 14:26 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/10 "2020-04-18T14:26:45Z")
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....
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [18 April 2020 14:51 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/11 "2020-04-18T14:51:57Z")
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.
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [18 April 2020 15:10 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/12 "2020-04-18T15:10:33Z")
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...
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [18 April 2020 15:29 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/13 "2020-04-18T15:29:06Z")
> [@Mrfox](#):
>
> To do the calculations in Node Red means that you for example use functions like Context?
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.
> [@Mrfox](#):
>
> Are you getting energy in consumption from the meter or do you just get a meter stand like I do?
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](https://discourse.nodered.org/t/contextmenu-location/22780/45)
---
### Author:  [@borpin](https://discourse.nodered.org/u/borpin)
#### Post date: [18 April 2020 22:21 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/14 "2020-04-18T22:21:27Z")
What gauges are those? Are they in a NR dash?
---
### Author:  [@Paul-Reed](https://discourse.nodered.org/u/Paul-Reed)
#### Post date: [18 April 2020 22:35 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/15 "2020-04-18T22:35:32Z")
They are @hotNipi's `node-red-contrib-ui-artless-gauge`'s in a NR dashboard.
I really like them, simple modern styling.
---
### Author:  [@borpin](https://discourse.nodered.org/u/borpin)
#### Post date: [19 April 2020 05:51 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/16 "2020-04-19T05:51:21Z")
> [@Paul-Reed](#):
>
> They are @hotNipi's `node-red-contrib-ui-artless-gauge`
Boom moment - exactly what I have been searching for - a multi sector gauge. 😀 😀.
---
### Author:  [@edje11](https://discourse.nodered.org/u/edje11)
#### Post date: [19 April 2020 08:31 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/17 "2020-04-19T08:31:20Z")
> [@Mrfox](#):
>
> Is it possible to give a time window from now()? Like (now() - 50m to 70m) ?
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 [https://timeseriesadmin.github.io/](https://timeseriesadmin.github.io/)
---
### Author:  [@Mrfox](https://discourse.nodered.org/u/Mrfox)
#### Post date: [19 April 2020 15:26 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/18 "2020-04-19T15:26:37Z")
Thank you edje11! That worked!!! Now I can compare the latest and the second latsest value. Great work! Thanks alot everyone!!!
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [18 June 2020 15:26 UTC](https://discourse.nodered.org/t/calculate-hourly-consumption/25035/19 "2020-06-18T15:26:43Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.