Hi, Wanted to first off say Thanks to all who put in work to make Grafana happen, what an amazing tool. Eternally grateful.
I’m trying to create a dashboard that shows the total power used in kWh. I have the kW demand, as the equipment draws power over time, I see the energy used, but it is not calculated. I’ve searched and found a few power dashboards, but I tried them as well and can’t get it to work.
I’ve tried few things and can’t figure this out. I’m running Grafana 5.0 and influxDB.
In theory you need to do a math with value field based on your time granularity and then CUMULATIVE_SUM(). Example for 1 m time granularity:
SELECT CUMULATIVE_SUM(value*1/60) ....
Thanks for the reply. I tried the query and got this error.
Query:
SELECT cumulative_sum(value*1/60) FROM "jace.home.power" WHERE ("entity_id" =
'incoming_meter.kw_demand') AND time >= now() - 6h GROUP BY time(1d)
And got the error:
aggregate function required inside the call to cumulative_sum
That’s correct, it was just theory. You have data, so you need to find correct syntax. I guess (=in theory again) subqueries can help here.
Thanks for the reply. I’ve tried few things and I’m more confused than when I started. Attached is a grafana singlestat, and the other image is taken at the same time where the same kW demand is totalized on a industrial PLC. Same kW demand is used to calculate kWh over time. I would expect the current day Total be the same 24.8 kWh between the industrial plc and grafana.
I do not know anything about influxdb, but i think that something like this can help you:
in singlestat panel:
SELECT mean(“value”) FROM “jace.home.power” WHERE (“entity_id” =
‘incoming_meter.kw_demand’ and $timeFilter GROUP BY time(1h)
and in the Option-Stat:Total
In time range:now/d
What is your logic behind SUM(value)/1000?
Read around the forums to convert to watts then calculate the kWh, would work better. but doesn’t make a difference.
Thank you, values are defiantly closer, I’m starting to understand. The one thing I don’t get is influxDB is time stamping in UTC. and grafana has the time zone to use local browser time. So in grafana a new day starts at 5pm instead of 12am. I thought if the group by is set to 1d, that it would convert to PST 12am-12am is 1day.
The query to influxdb would automatically convert from UTC to PST. I would expect the daily values reset to 0 kWh at 12am PST. but they reset at 5pm PST, if I query influx db with influxdb studio, I can see the new day starts at 12am UTC.
From all the reading I have done, always mentions to leave influxDB time stamping in UTC then the app in the case is grafana to adjust for the time shift.
Thank you,
So I had this:
SELECT mean(“value”) FROM “jace.home.power” WHERE (“entity_id” =
‘incoming_meter.kw_demand’ and $timeFilter GROUP BY time(1h)
and this: in the Option-Stat:Total
The missing key was this which I did not have: In time range:now/d
After adding the now/d in time range, brought the values dead on, and exactly the same between the PLC and grafana.
Now what does it do so I can understand it better.
Missing Key was the now/d in time range for month range it’s now/M . Now Grafana shows what I’m looking for. My previous screencap was reading Last 1 Day but now reads Today so Far.
Or used to read Last 1 Month, now reads This month so far.
Thank you all,
if you have an energy consumption of X [kWh], this means that during 1h the average power is X. Based on this, if you have the average power during each hour, this same value or number (kW = kWh) is equal to the energy.
from then on, you already have the energy consumed every hour, and it is only that you select the time interval that interests you. this due to this, that when putting now / d it works for the current day.
if you make a graph with:
SELECT mean(“value”) FROM “jace.home.power” WHERE (“entity_id” = ‘incoming_meter.kw_demand’ and $timeFilter GROUP BY time(1h)
what you will get will be the hour-by-hour consumption of your equipment.
I hope you understand me,
Finally, tell you that you have a very nice dashboard.
Hello @grafjace
Is it possible to post some print screens of your Single Stat configuration?
I want to do a panel similar to yours but I can’t get the single stat display right.
Any idea how to calculate from power form other variables?
If temp > 30 pump working.
Example:
SELECT mean(“temp_input”)/mean(“temp_input”) FROM “sensors” WHERE (“feature” = ‘temp1’) AND “temp_input”> 30 AND $timeFilter GROUP BY time($__interval) fill(null)
Pump power is 2,5kW, now I need working time
This aproach isnt hard, but is imprecise.
you can do something like this:
SELECT SUM(aux)
FROM (
SELECT 2.5*(mean("temp_input")/mean("temp_input"))/60 as "aux"
FROM "sensors"
WHERE ("feature" = 'temp1') AND "temp_input"> 30 AND timeFilter
GROUP BY time(1m)
)
GROUP BY time(1h)
On this query, “2.5” means the power of pump, but it depends of many factors so the energy could vary.
Thank you @danielgonzalez that is this 
I made litle update for new visualisation
SELECT CUMULATIVE_SUM(SUM(aux))
Here is full example how to measure and calculate energy.
I have a variant of this issue I would like to solve, but I’m stuck at the moment:
How do I measure kwh from kw measurements in a variable time frame?
My setup is as following: I have a power meter giving me total power for my house and I have a lot of power sensors all around my house. That gives me total power, known power usage and unknown power usage. What I’m interested in is doing kwh measurements in the unknown power usage to try to analyse what euipment uses that power and how much power is used when the equipment is on.
One example is when charging my car, that makes a very distinct curve in my unknown power usage graph that I can isolate to measure approximately how many kwh is used by the car and then calculate the cost for charging my car.
Any ideas?
Dumb question (I am in Grafana 8.x) but where are in the Option-Stat:Total and: In time range:now/d ?
