How to persist data average every 5 minutes? [solved]

rules
openhab2
persistence
Tags: #<Tag:0x00007fd31236d408> #<Tag:0x00007fd31236d278> #<Tag:0x00007fd31236cf30>

(Luca Battistella) #1

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?

Thanks in advance for your advice


(Jürgen Baginski) #2

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.


(Rich Koshak) #3

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.

(Luca Battistella) #4

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.


(Luca Battistella) #5

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?


(Rich Koshak) #6

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.


(Luca Battistella) #7

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.

Thank again for all the help!