Hi there,
i’m pretty new to openhab and the reason of this post is not to have a working out-of-the-box solution, but to understand how i can implement this thing:
I have a Raspberry Pi that sends over MQTT the istant values of elecricity consumption, and this works well and stores data in rrd4j persistence.
My idea is to implement InfluxDB+Grafana for better charting so my question is:
What’s the best way to store in InfluxDB the average of the istant values every 5 minutes?
I mean data starts to arrive on Openhab at xx.00; at xx.05 i have 5 minutes of data and i want to store just the average of this values.
I was thinking of stores the data from 00 to 05 minutes somewhere, and then calculate the average and stores the result on Db, but maybe there is a way to do this average on-the-fly.
What do you think? any better ideas?
You could use rrd4j for persisting data. It can be configured to store every minute in the first archive and have the average of the last five minutes in the second.
The downside is that you need to state for which timeframe you want to keep the data. Any value that is older then the stated timeframe is lost.
Are you looking for a rolling average or just an average every five minutes?
For example, a rolling average would calculate a new average every time a new value is received on the MQTT topic. An average every five minutes would set a schedule to calculate the average every five minutes no matter how many values were received.
I would let persistence do most of the work for me and let it continue to store the “raw” values and use averageSince to calculate the average based on those stored values.
Create a new Item to represent the average and persist that to InfluxDB.
Rolling Average Implementation:
Create a rule that triggers when the MQTT Item receives an average
MQTT_FiveMinAvg.postUpdate(MQTT_Value.averageSince(now.minusMinutes(5), "rrd4j") // assumes values are still in rrd4j
Every Five Minutes Implementation:
Create a rule with a Time cron trigger to fire every five minutes
Same line as above to calculate the 5 minute average and post it to the average Item.
Thanks for all the responses. @rlkoshak the idea is to have an average of data every 5 minutes, maybe when the minutes terminates on 0 or 5 (ex 5, 15, 30) just for some kind of order.
By the way thanks for the help, i will work on it and post the various script here to help someone else at the beginning like me.
I work on rules but it gives me some errors:
Here is the meteravg.rules
rule "Power average"
when
//Time cron "0 0/5 * * * ?"
Time cron "0 * * * * ?" //for debug
then
var Number PowerAverage = Consumption.averageSince(now().minusMinutes(5))
ConsumptionAvg.postUpdate(PowerAverage) // assumes values are still in rrd4j
// logDebug("consumptionAvg", "Cons average {}", ConsumptionAvg.state)
PowerAverage = SolarPowerTot.averageSince(now.minusMinutes(5))
SolarPowerAvg.postUpdate(PowerAverage) // assumes values are still in rrd4j
// logDebug("SolarAvg", "Solar average {}",SolarPowerAvg.state)
end
rule "SecPower average"
when
Item ConsumptionAvg changed or
Item SolarPowerAvg changed
then
if(SolarPowerAvg.state > ConsumptionAvg.state ) {
var Number diff = SolarPowerAvg.state as Decimal - ConsumptionAvg.state as Decimal
ImmissionAvg.postUpdate(diff)
SelfConsumptionAvg.postUpdate(ConsumptionAvg.state)
FetchEnergyAvg.postUpdate(0)
}
else if (SolarPowerAvg.state < ConsumptionAvg.state) {
ImmissionAvg.postUpdate(0)
SelfConsumptionAvg.postUpdate(ConsumptionAvg.state)
var Number diff = ConsumptionAvg.state as Decimal -SolarPowerAvg.state as Decimal
FetchEnergyAvg.postUpdate(diff)
}
end
The first rule work ok and saves data on influxdb every 1 minutes (is for debug), but the second rule doesn’t work and gives me (on log) : Rule 'SecPower average': void about 2 times, and i don’t know what it means.
Anyone help?
Cast to Number, not Decimal. Or if you prefer, DecimalType. I don’t think the state of a NumberItem is of type Decimal. I’m not even certain there even is a class named Decimal.
Also cast the states to Numbers in your if statements.
thanks that solves the problem! Here the final meteravg.rules
rule "Power average"
when
Time cron "0 0/5 * * * ?"
//Time cron "0 * * * * ?" //for debug
then
var Number PowerAverage = Consumption.averageSince(now().minusMinutes(5)) as Number
ConsumptionAvg.postUpdate(PowerAverage) // assumes values are still in rrd4j
// logDebug("consumptionAvg", "Cons average {}", ConsumptionAvg.state)
PowerAverage = SolarPowerTot.averageSince(now.minusMinutes(5)) as Number
SolarPowerAvg.postUpdate(PowerAverage) // assumes values are still in rrd4j
// logDebug("SolarAvg", "Solar average {}",SolarPowerAvg.state)
end
rule "SecPower average"
when
Item ConsumptionAvg changed or
Item SolarPowerAvg changed
then
if(SolarPowerAvg.state > ConsumptionAvg.state ) {
var Number diff = SolarPowerAvg.state as Number - ConsumptionAvg.state as Number
ImmissionAvg.postUpdate(diff)
SelfConsumptionAvg.postUpdate(ConsumptionAvg.state as Number)
FetchEnergyAvg.postUpdate(0)
}
else if (SolarPowerAvg.state < ConsumptionAvg.state ) {
ImmissionAvg.postUpdate(0)
SelfConsumptionAvg.postUpdate(SolarPowerAvg.state as Number)
var Number diff = ConsumptionAvg.state as Number - SolarPowerAvg.state as Number
FetchEnergyAvg.postUpdate(diff)
}
end
EDIT: I followed the guide on this community for Openhab with InfluxDB+Grafana and I wrote down what i have done to achieve this result in my little blog here. I’m sorry for the Italian in the “article” but i think google translate will make the stuff.
I’ll share this for everyone will face off with my same problems.