Background issue is - have accurate energy consumption graphs.
Source data comes from Tasmota, via MQTT, Telegraph, InfluxDB and visualized with Grafana.
Tasmota reports following data (on top of others, though energy related):
Today - in Wh, consumed today so far,
Yesterday - in Wh, total yesterday (last value of today, reset at midnight),
Power - in W, power consumption at the time,
Period - Wh consumed since last measurement.
The first two are simple - just simple values, can be graphed, etc.
The Period one, when accumulated over the day, should result in total Wh consumed over the day.
Seems easy, so far.
Tasmota, is set up to report:
on every power consumption change bigger than 5W
5 minutes
InfluxDB therefore has multiple values, not necessarily at the same time, i.e. at the top of an hour and every 5 or ten minutes.
Using “Group by” and having interval set i.e. to 10 mins, should give us roughly around accurate “measurements” at the time/close to.
Big question is - how to have calculate accurate Wh consumption so far and over the day out of “Power” - is it possible at all? Maybe Tasmota needs to be changed to report only every 5 minutes? Feeling is that it should be possible to workaround the fact that Tasmota reports at various moments and normalize to interval values.
The value produced should be relatively close to Today/Yesterday values for given days.
Same and even more important is that summing all Period values, doesn’t give us on the graph what pops up as Today or then is recorded as yesterday value.
Question is how? I’ve spent number of hours on this and started to run in circles now, feeling like around the target which did hide in front of me.
SELECT mean("ENERGY_Power") FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval) fill(null)
SELECT cumulative_sum(mean("ENERGY_Power")) / 10 FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval) fill(null)
SELECT sum("ENERGY_Period") FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time(1d)
SELECT last("ENERGY_Today") *1000 FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval)
SELECT last("ENERGY_Yesterday") *1000 FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval)
Sorry, I have no idea how to implement these as tried everything already and became clueless at the moment - hence seeking for help (no, am not lazy or anything like this - just clueless in this case).
Why you are filling with null, when I you have received recommendation for previous value?
SELECT mean("ENERGY_Power") FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval) fill(previous)
So you have “normalized” data - value for each time bucket.
Integral:
SELECT INTEGRAL(power) FROM (
SELECT mean("ENERGY_Power") AS power FROM "smarthome" WHERE ("sensor"::tag = 'lodowka') AND $timeFilter GROUP BY time($__interval) fill(previous)
)
GROUP BY time($__interval)
Last one will be your work. Please post it here nicely readable formated.
Thanks, I’ll have another look and come back to you. Appreciate the help. (sorry did run out of time on private side and had emergency today - need to resolve the local interrupt).
