I have an influxDB database where I collect electrical consumption data from a multichannel metering device (Iotawatt) and solar production information from the SolarEdge cloud through API, passed over to the same DB by Home Assistant.
My objective now is to obtain an aggregated (1day) view of my export (negative) and import (positive) electricity energy (kWh), so that I can evaluate the savings obtainable by adopting a battery.
I don’t have data at the main meter, but only the consumption of individual loads (from Iotawatt).
So what I want to do is:
- sum instantaneous (1s) values of power consumption (in kW) from all Iotwatt channels
- deduct from there the instantaneous (1s) value of solar production (in kW) to obtain an instantaneous (1s) net value (positive = consumption, negative = export to the grid).
- aggregate this net power information to come to an energy information in kWh per day.
I have been trying hard to find a solution myself, but I can only achieve 1 and 3. I am not able to achieve step 2 of summing up the two series, so my query ends up in delivering 2 sets that are displayed separately. See example (in this case I used 1m for step #3, to see more details).
Any help will be greatly appreciated! Probably the problem is coming from the way the data flows into the DB, but I did not pay too much attention when I created the setup… Now it’s late.
SELECT (mean("sommapotenza")-mean("sun")) /60 AS "net" FROM ( SELECT sum("Watts") AS "sommapotenza" FROM "autogen"."IotaWatt" WHERE $timeFilter GROUP BY time(1s) fill(none) ), ( SELECT mean("value") AS "sun" FROM "autogen"."W" WHERE ("entity_id" = 'solaredge_current_power') AND $timeFilter GROUP BY time(1s) fill(linear) ) WHERE $timeFilter GROUP BY time(1m) fill(0)
Result: (as you can see, NET is split in 2, one being the sum of Iotawatt channels and the other being the Solaredge info).