I’ve experimented with several sensors to measure engery, water and such. While doing this i got a lot of wrong sensor values, making the history look ugly. Is there a way to repair such things?
See the gap in here? The values needs to be shifted up to form a nice line again.
If these are statistics then you can try via devtools > statistics but I guess this is not.
Then the only way is in the db itself. Not sure what you are using as recorder but if this is the only set of data to repair then this is not too much and without SQL you can do this manually using a tool as dbbrowser (sqlite) of phpmyadmin (mariadb)
Would that help me?
Nope… data is stored in teh db, both recent data as ‘history’ for as long as the data has not been purged (default 10 d) or long term statistics…which are kept in a much lower frequency and much longer (not wanting to say perpetual)
So, the only option is …tweak the db data…not for the fainthearted though but also no rocket science…what recorder do you have and/or do you know how to get to the db ?
I’ve installed the sqlite plugin which provides access to the db.
and…? can you enter the db and see the data in ‘statistics’ table?
Are you any good at SQL?
I think i’m not a novice in SQL but i have trouble finding the data.
First i looked into statistics_meta to find the meta_id of my sensor
SELECT * FROM statistics_meta WHERE statistic_id LIKE ‘%wasser%’
It shows as ID 41. Now join this to the statistics table:
SELECT * FROM statistics_meta AS sm, statistics AS s WHERE s.metadata_id=sm.id AND sm.statistic_id = ‘sensor.wasserzahler’
This gives me all stored results for the given sensor. Then i tried to find by wrong value:
SELECT * FROM statistics_meta AS sm, statistics AS s WHERE s.metadata_id=sm.id AND sm.statistic_id = ‘sensor.wasserzahler’ AND s.state < 400
Interestingly i only find one value…
| id | statistic_id | source | unit_of_measurement | has_mean | has_sum | name | id | created | start | mean | min | max | last_reset | state | sum | metadata_id |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 41 | sensor.wasserzahler | recorder | m³ | 0 | 1 | NULL | 44397 | 2022-12-21 17:00:10.235769 | 2022-12-21 16:00:00.000000 | NULL | NULL | NULL | NULL | 0.001 | 3.8010000000000446 | 41 |
Your picture all above shows values above 400 that were wrong, are we talking of the same ?
Also note that you are possibly looking history vs. statistics. History is kept for as long as the recorder is configured (default 10d), statistics much longer…hence I was asking if you wanted to correct statistics or history, seems to be the latter ? If it is the latter then this problem will resolve itself in hte next days (check your recorder settings)
History is in the states table and there you may find a lot more for the same sensor as it stores with every update.
