Maednix
(Maednix)
September 22, 2019, 3:48pm
1
Hi,
i am using OH2 on WIN 10.
I wrote a rule to get results from a SQL database with EXEC Binding.
Everything seems to work fine but i can not understand the different results from OH averageSince and the SQL statement.
When i Query via SQL
SELECT sum(value), count(value), AVG(value) FROM openhab.Item100 WHERE TIME>'2019-09-22 00:00:00.000'
i get these values
+------------+--------------+--------------------+
| sum(value) | count(value) | AVG(value) |
+------------+--------------+--------------------+
| 1329 | 79 | 16.897435897435898 |
+------------+--------------+--------------------+
this result is correct (1329/79=16.89)
when i query OH2 on a rule with
logInfo("execBinding", "Result sumSince:" + Sonoff_Power_Wz_Watt.sumSince(parse("2019-09-22T00:00:00")))
i get
Result sumSince:1329.0
result is correct
but on query
logInfo("execBinding", "Result averageSince withTimeAtStartOfDay:" + Sonoff_Power_Wz_Watt.averageSince(now.withTimeAtStartOfDay()))
i get the result
2019-09-22 17:50:38.945 [INFO ] [execBinding] - Result averageSincewithTimeAtStartOfDay:11.90627495839114
Why i get a different result???
What does the averageSince different from SQL?
Thanks for any advice
rossko57
(Rossko57)
September 22, 2019, 5:01pm
2
What happens if you use the same time?
Sonoff_Power_Wz_Watt.averageSince(parse(“2019-09-22T00:00:00”))
Maednix
(Maednix)
September 22, 2019, 5:17pm
3
Same Thing.
No Change
I was just about debugging the persistence service
The mysql query from the logs seems to be correct.
And the returned rows also.
Only the calculationof the average value is defitively wrong.
Here some results.
logInfo("Your command exec", "Result averageSinceparse:" + Sonoff_Power_Wz_Watt.averageSince(parse("2019-09-22T00:00:00")))
logInfo("Your command exec", "Result averageSincewithTimeAtStartOfDay:" + Sonoff_Power_Wz_Watt.averageSince(now.withTimeAtStartOfDay()))
have the same output on log files
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM Item100 WHERE TIME>'2019-09-22 00:00:00' ORDER BY time ASC
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM Item100 WHERE TIME>'2019-09-22 00:00:00' ORDER BY time ASC
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '69.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '30.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '12.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.065 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '25.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.080 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '111.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.080 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '67.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.080 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '77.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.080 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '96.0', getClass = 'class java.lang.Double', clazz = 'Double'
2019-09-22 19:05:53.080 [DEBUG] [nhab.persistence.jdbc.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '30.0', getClass = 'class java.lang.Double', clazz = 'Double'
....................... and so on
2019-09-22 19:05:53.143 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: query for Sonoff_Power_Wz_Watt returned 92 rows in 78 ms
2019-09-22 19:05:53.158 [INFO ] [thome.model.script.Your command exec] - Result averageSincewithTimeAtStartOfDay:11.73832348361359
2019-09-22 18:55:43.206 [INFO ] [thome.model.script.Your command exec] - Result sumSince:1477.0
I pasted only the first values
There are 92 Rows in Total but the Average calculation is different from MYSQL
+------------+--------------+--------------------+
| sum(value) | count(value) | AVG(value) |
+------------+--------------+--------------------+
| 1477 | 92 | 16.054347826086957 |
+------------+--------------+--------------------+