Hallo,
I’m collecting houerly energy consumption values with JDBC persistence (mySQL).
e.g.
If I want to visualise it with a chart it looks like this:
The strange thing is if I select today everything looks ok, if I go back the days there is just nonsene.
What am I doing wrong?
It seems that JDBC is also reading correct, but the visualisation is wrong. OH is 4.2.1.
2024-09-27 08:11:39.395 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC::query: item is StromHaus_Verbrauch
2024-09-27 08:11:39.397 [DEBUG] [persistence.jdbc.internal.JdbcMapper] - JDBC::getHistItemFilterQuery filter='true' numberDecimalcount='3' table='item0002' item='StromHaus_Verbrauch (Type=NumberItem, State=0.33984375, Label=Strom Haus Verbrauch, Category=energy, Groups=[gPersistenceMysql, gStromVerbrauch])' itemName='StromHaus_Verbrauch'
2024-09-27 08:11:39.399 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::getHistItemFilterQueryProvider filter = FilterCriteria [itemName=StromHaus_Verbrauch, beginDate=2024-09-24T22:00Z, endDate=2024-09-25T21:59:59.999Z, pageNumber=0, pageSize=2147483647, operator=EQ, ordering=ASCENDING, state=null], numberDecimalcount = 3, table = item0002, simpleName = StromHaus_Verbrauch
2024-09-27 08:11:39.401 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::query queryString = SELECT time, value FROM item0002 WHERE TIME>='2024-09-25 00:00:00' AND TIME<='2024-09-25 23:59:59' ORDER BY time ASC
2024-09-27 08:11:39.402 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::doGetHistItemFilterQuery sql=SELECT time, value FROM item0002 WHERE TIME>='2024-09-25 00:00:00' AND TIME<='2024-09-25 23:59:59' ORDER BY time ASC
2024-09-27 08:11:39.446 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.23828125', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.447 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.23046875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.449 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.26171875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.450 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.26171875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.452 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.20703125', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.453 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.28515625', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.455 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.85546875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.456 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.4296875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.458 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.18359375', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.459 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.109375', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.460 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.03515625', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.462 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.00390625', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.463 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '38747.98', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.465 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.37109375', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.466 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.19921875', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.467 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.51953125', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.469 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.9140625', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.470 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.36328125', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.472 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.375', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.473 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.40625', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.475 [DEBUG] [istence.jdbc.internal.db.JdbcBaseDAO] - JDBC::ItemResultHandler::handleResult getState value = '0.43359375', unit = 'null', getClass = 'class java.lang.Double', clazz = 'Double'
2024-09-27 08:11:39.476 [DEBUG] [jdbc.internal.JdbcPersistenceService] - JDBC: Query for item 'StromHaus_Verbrauch' returned 21 rows in 79 ms
Are you sure that you have set the proper default persistence for the MainUI. You can have many different persistence options, but MainUI charts will only use whichever one is set as the default for the analyzer charts.
Yes, default persistence service is correct. Otherwise the chart would be empty. I almost assume it is a bug.
That depends on whether or not you have changed the default settings, and you had not mentioned that you have. rrd4j is installed by default and set to the default MainUI persistence source. So, just because your charts are not empty does not mean that you have set the default persistence to jdbc correctly.
It’s possible, but I don’t think you have quite presented enough evidence to make this clear, or narrow down where the bug might be. Is it only when the chart period is set to days that the problem occurs? If you set the chart period to one week is the data from past days correct? Can you correctly extract the data using a persistence action in a rule? Does the problem only appear with this item or is it all items?
This is the default setting:
I actually also tried the other way without configuring it over Analyzed and set the persistence service in the aggregate series:
No. Same issue in Month of Year, Day of Week, Day of Month.
No.
See post 2. I have set log level of jdbc to DEBUG and then clicked on the chart. The data was read correctly, but not shown correctly.
log:set DEBUG org.openhab.persistence.jdbc
Another JDBC item with the aggregaten “Difference of lasts” is working, but this item where I’m using “Sum” does not display the right chart.
Right, but that doesn’t fully narrow down where the error is, only that the jdbc binding itself is getting the correct values. If you can access correct data via a persistence action in a rule, but not in the chart then you know with pretty high certainty the error is in the UI charting code.
Ah, that’s pretty telling, then. With that information, it would be useful to dig down just a little deeper into the differences between the two items. Particularly, in this case if they have state descriptions or unit metadata. Persistence is now unit aware in recent versions and it’s quite possible that the sum function in UI isn’t fully taking that into account.
This does sound, at this point, as if it’s worth filing an issue on the UI repository. Especially if you can add that additional item Type/Unit information.
The persistence REST endpoint does not return the unit, just the values in the default unit as defined through the unit metadata (or following the system default).
So the UI cannot take unit into account …
