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 :confused: 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
    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

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!

I already had a mariadb server running, I’m using that to store everything:

* : strategy = everyChange, every5Minutes, restoreOnStartup

Costs a little more then 1MB every day for now.