maximumSince returns value, averageSince is null

Add-ons Persistence Services
,
1

Hi all,
I’m struggeling with persistence in rules, but can’t find the reason why it is failing. In short (as in title): calling maximumSince returns a value, but averageSince doesn’t.

openHAB version: 3.3.0 release (offical docker image), using both MapDB and JDBC (MariaDB).

My item:

Number Balkon_Sensor_Wetter_Luftfeuchtigkeit "Luftfeuchtigkeit"  <humidity> (StoreFiveMinutes)                {channel="deconz:humiditysensor:homeserver:BalkonMultisensorLuftfeuchtigkeit:humidity"}

My test rule:

rule "Testing average and maximum"

when
  Time cron "*/30 * * * * ?"
then
  logInfo("myRule", "maximum {}", Balkon_Sensor_Wetter_Luftfeuchtigkeit.maximumSince(now.minusMinutes(10), "jdbc").state)
  logInfo("myRule", "average {}", Balkon_Sensor_Wetter_Luftfeuchtigkeit.averageSince(now.minusMinutes(10), "jdbc"))
end

The output:

18:33:00.037 [INFO ] [org.openhab.core.model.script.myRule ] - maximum 38.59
18:33:00.049 [INFO ] [org.openhab.core.model.script.myRule ] - average null

Any idea what I’m doing wrong?

Edit: According to this thread it may be a logging issue, but calling toString() results in:

18:42:30.816 [ERROR] [.internal.handler.ScriptActionHandler] - Script execution of rule with UID 'Testregel-1' failed: cannot invoke method public java.lang.String org.openhab.core.library.types.DecimalType.toString() on null in Testregel
2

Are you sure there is a valid value 30 minutes ago? If not, no average can be calculated. A maximum might still be possible.

3

What is the definition of “valid”? In my naive thinking, if there is a max value (and this is the only one), shouldn’t this be the average, too?

Latest values, extracted via phpMyAdmin:

Screenshot 2022-07-03 at 19.00.06
Screenshot 2022-07-03 at 19.00.06880×814 162 KB

Note: For some reason, database times are -2 hours, but I expect this to be some “internal detail” of the JDBC binding, right?

4

Can you please try to set the interval to 15 minutes? For me it seems to work fine.

5

You could check the timestamp of the ‘max since’ result returned, as well. I’m thinking max since might return a value from some earlier time, while no records exist for the designated time period. Just to make sure you see what you expect there.

6

@J-N-K
That way?

[...]
then
  logInfo("myRule", "maximum {}", Balkon_Sensor_Wetter_Luftfeuchtigkeit.maximumSince(now.minusMinutes(15), "jdbc").state)
  logInfo("myRule", "average {}", Balkon_Sensor_Wetter_Luftfeuchtigkeit.averageSince(now.minusMinutes(15), "jdbc"))
end

results in

20:12:30.325 [INFO ] [org.openhab.core.model.script.myRule ] - maximum 39.7
20:12:30.337 [INFO ] [org.openhab.core.model.script.myRule ] - average null
7

@rossko57

logInfo("myRule", "maximum {}", Balkon_Sensor_Wetter_Luftfeuchtigkeit.maximumSince(now.minusMinutes(15), "jdbc").getTimestamp().toString())

results in

20:15:00.658 [INFO ] [org.openhab.core.model.script.myRule ] - maximum 2022-07-03T20:15:00.657312+02:00[Europe/Berlin]
8

Please use log:set DEBUG org.openhab.persistence.jdbc.internal.JdbcPersistenceService on the karaf console and show what is actually returned. You can use DEFAULT to disable logging later.

It’s interesting that the timestamps in my setup are local time, while they seem to be UTC in your example. I’m using JDBC with mariadb backend.

9

Done

20:22:30.449 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Balkon_Sensor_Wetter_Luftfeuchtigkeit
20:22:30.460 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Query for item 'Balkon_Sensor_Wetter_Luftfeuchtigkeit' returned 0 rows in 4 ms
20:22:30.466 [INFO ] [org.openhab.core.model.script.myRule ] - maximum 39.7
20:22:30.468 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Balkon_Sensor_Wetter_Luftfeuchtigkeit
20:22:30.473 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Query for item 'Balkon_Sensor_Wetter_Luftfeuchtigkeit' returned 0 rows in 2 ms
20:22:30.478 [INFO ] [org.openhab.core.model.script.myRule ] - maximum 2022-07-03T20:22:30.476505+02:00[Europe/Berlin]
20:22:30.480 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC::query: item is Balkon_Sensor_Wetter_Luftfeuchtigkeit
20:22:30.483 [DEBUG] [.jdbc.internal.JdbcPersistenceService] - JDBC: Query for item 'Balkon_Sensor_Wetter_Luftfeuchtigkeit' returned 0 rows in 1 ms
20:22:30.485 [INFO ] [org.openhab.core.model.script.myRule ] - average null

Wait - 0 rows?

10

That’s the explanation: there are no values returned. In this case the maximum is the current value. But the average needs at least two values to calculate it. The average calculation is integrating over all values, calculating the sum of (value2 + value1)/2 * (timestamp2-timestamp1) and dividing that by now-firstTimestamp. If you have only the current value, this fails.

I already edited my post above: in my setup the dates are stored in local time, not UTC like in your example. But this seems to be an issue with the JDBC add-on, I can’t comment on that.

11

I changed the timezone of the MariaDB docker container (which was UTC), now it works! Thank you really much!

Still - shouldn’t there be any explanation in the persistence article that if there are no matching values, maximumSince returns current value, while averageSince returns null? :thinking:

12

Feel free to suggest an improvement to the documentation: openhab-docs/persistence.md at main · openhab/openhab-docs · GitHub

You can use the pencil at the top to use the web editor.

1 Like