My Synlogy is running OpenHab on Docker and MariaDB as persistence.
Everything is running fine, but for further improvements I need some additional items which can be calculated by the existing data.
Assuming I have three different temperature sensors. The values are updated in the database on every update.
What i would like to do is calculation the average temperature for every timestamp. Therefore I have a rule which calculates the average on ervery update. Assuming that I have data of the last year, the rule calculates the average only since a certain time.
I would like to create the average of three different room temperatures. For every timestamp I need an average temperature of the three rooms. MY Data is too Big for Excel Is there any SQL-Query to do this?
and add agvTemps (without a *) to your persistence. This will calculate the average and save it to your persistence on every update to any of the sensors.
Beyond that you will probably have better luck searching for or posting to a MariaDB or SQL database forum. This is a MariaDB specific problem and while MariaDB is used by many on this forum, they are in the minority.
Yes i found a solution.
Just replace sensor_rt_ziel and sensor_table_a, sensor_rt_table_b with your table names.
Example for Averaging 2 Tables:
INSERT INTO sensor_rt_ziel
select
table_a.time,
(
table_a.value
+ (select coalesce(min(table_b.value),0.0) from sensor_rt_table_b as table_b where table_b.time =
(select max(table_b2.time) from sensor_rt_table_b as table_b2
where table_b2.time <= table_a.time))
) /2 as average
from sensor_rt_table_a as table_a LIMIT 0,5 ON DUPLICATE KEY UPDATE value=table_a.value;
INSERT INTO sensor_rt_ziel
select
table_a.time,
(
table_a.value
+ (select coalesce(min(table_b.value),0.0) from sensor_rt_table_a as table_b where table_b.time =
(select max(table_b2.time) from sensor_rt_table_a as table_b2
where table_b2.time <= table_a.time))
) /2 as average
from sensor_rt_table_b as table_a LIMIT 0,5 ON DUPLICATE KEY UPDATE value=table_a.value;
Should get you the average for the last year
You don’t need to specify the persistence service unless you want to use a particular one of if your default service doesn’t support the method like mapdb.
I have multiple temp sensors in my living room. I want their average to calculate the room temperature. Your example gives the average from 1 item.
I do use that to calculate the average temperature and humidity, so I can see if the temp / humidity is rising or falling:
rule "Temperature Woonkamer Average"
when
Item Temp_Woonkamer received update
then
Temp_Woonkamer_Average.postUpdate(Temp_Woonkamer.averageSince(now.minusHours(6)))
end
rule "Humidity Woonkamer Average"
when
Item Humid_Woonkamer received update
then
Humid_Woonkamer_Average.postUpdate(Humid_Woonkamer.averageSince(now.minusHours(6)))
end
Make sure the group itself is persisted in your strategies section.
rule "group temp changed"
when
Item LivingRoomTemperatures changed
then
averageLivingRoomTemps.postUpdate(LivingroomTemperatures.averageSince(now.minusDays(365), "influxdb"))
end