It isn’t OK. ‘Dishwasher / Refridgerator’ is daily sum exactly from device.
‘Dishwasher / Refridgerator 2’ is from *24/1000 group 24h
Here: ‘Refridgerator / Dishwasher total’ Is sum from device (‘Dishwasher / Refridgerator’ from previous image)
‘Refridgerator / Dishwasher’ is Watts used to calculate ‘Dishwasher / Refridgerator 2’ in previous image
Generally:
- use Integral function to calculate WH from W - first aggregation
- use Sum function with daily time agggregation - second aggregation
=> 2 aggregations are required = subqueries are required = you need to switch to raw query editor in Grafana and you need to write it on your own
I’m not familiar with raw query:
SELECT integral("value") FROM "W" WHERE ("entity_id" = 'power_1_current_watts') AND $timeFilter GROUP BY time(24h) fill(none)
Can you help me with it?
I don’t know, sorry.
Thanks @jangaraj, that’s what I did originally, but it slowed down Grafana significantly.
Grafana was showing the ‘busy’ spinning wheels almost constantly, which was confirmed by running top.
Changing to my current solution reduced the processor demand to a fraction of what it was previously.
(Raspberry Pi)
Yes, subqueries + integral calculation + time aggregation can be expensive and IMHO Raspberry Pi is a slow device to do that. You can use continues queries to “precalculate” data. Or the best option is to insert already precalculated data, so no computation will be required on the InfluxDB level - only simple select, so you will have a good query response time.
That is practically what I’m doing now, the pre-calculation is done by node-RED, and the influx query merely adds them together in the timeframe selected, which the Pi manages very easily.
I am also using Continuous Queries to downsample data, so historical data doesn’t have the same precision, which again doesn’t stress the Pi when I retrieve data spanning days, weeks, months etc.
Does anyone have a tip on how to get total kWh from my data - I have power measurements every 30 seconds, so 2800 samples a day. If I do timeline of yesterday, I thought I would be able to do this and get an accurate count.
Here is what the raw data looks like. This is watt’s in use at a given sample time.
Hi Wilson3
Sorry for the long reply, I did missunderstud your question.
To ses the total kWh (for the last 20 years) you need to add the following:
Last year:
Last month:
This month so far:
This year so far:
my full dashboard:
Best regards
Henrik
1 Like
Hi Wilson3
what is your input value? Watt, kW or kWh??
If it is in W the math should be /3600000 (3600 sec/h and 1000W /kW
If it is in kW the math should be /3600 (3600 sec/h)
best regards
Henrik
Watts are what I have. I’ll give it a shot! Thanks!
1 Like
FWIW, I was scratching my head also to get kWh from infrequent readings from a zigbee plug, in Watts.
There are multiple posts around, with various solutions.
Here is what I came with, which is still simple and looks accurate:
SELECT SUM("power"::float) / 60.0 / 1000.0
FROM (
SELECT mean("value") AS power FROM "W" WHERE ("entity_id" = 'plug_xiaomi_1_electrical_measurement') AND $timeFilter GROUP BY time(1m) fill(previous)
)
GROUP BY time(1h)
Key is the subquery, which averages the Watts over 1 minute, which allows to simply do a SUM of those over 1h and divide by 60 (minutes) * 1000 (kilo), to get kWh.
5 Likes
HI everyone
Can you please help me here out.
I am monitoring a 3phase DB Board.
I need to calculate the power used over a months time.
I receive data every 4 seconds into the database.
this is an awesome solution and thanks for working it out to everyone’s benefit. The only catch is that it slows Grafana down to a crawl
