# Basic Calculation with different timestamps

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?

Table A:
0000-00-00 00:00:00.000 19.6
2018-01-22 20:45:31.594 19.6
2018-01-22 20:48:20.344 19.7
2018-01-22 20:50:54.567 19.8
2018-01-22 20:53:14.594 19.9
2018-01-22 20:55:20.087 20.0

Table B:
0000-00-00 00:00:00.000 18.4
2018-01-22 20:50:53.700 18.4
2018-01-22 20:53:38.417 18.5
2018-01-22 20:56:08.668 18.6
2018-01-22 20:58:24.422 18.7
2018-01-22 21:00:25.707 18.8

Table C:
0000-00-00 00:00:00.000 20.1
2018-01-22 20:47:07.669 20.1
2018-01-22 20:49:32.196 20.2
2018-01-22 20:49:42.184 20.2
2018-01-22 20:51:42.177 20.2

Result:
2018-01-22 20:45:31.594 (19.6+18.4+20.1)/3
2018-01-22 20:47:07.669 (19.6+18.4+20.1)/3
2018-01-22 20:48:20.344 (19.7+18.4+20.1)/3
2018-01-22 20:49:32.196 (19.7+18.4+20.2(/3

Going forward you can put your three Items into a

``````Group:Number:AVG avgTemps
``````

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.

Did you ever find a solution @nignac? I’ve the same challenge as you.

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;``````

The persistence service comes with this method:

`<item>.averageSince(AbstractInstant)`

Form the docs:

So for example:

``````averageItem.postUpdate(roomTempItem.averageSince(now.minusDays(365), "influxdb")
``````

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
then
Temp_Woonkamer_Average.postUpdate(Temp_Woonkamer.averageSince(now.minusHours(6)))
end

rule "Humidity Woonkamer Average"
when
then
Humid_Woonkamer_Average.postUpdate(Humid_Woonkamer.averageSince(now.minusHours(6)))
end``````

Thanks! I’ll try it out.

Put them in a group:

``````Group:Number:AVERAGE LivingRoomTemperatures
``````

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
``````

Good option! I never thought about that. I’m still learning the basics. Thanks!

`* : strategy = everyChange, every5Minutes, restoreOnStartup`