Trying to correct statistics in db but WHERE not working correctly?

[SAFP (Peter)]

I am trying to modify some statistics because of a rogue value and while I have the correct queries for statistics and statistics_short_term, something weird for states is happening. Here's my query:

SELECT * FROM states
WHERE (metadata_id > 8328 AND metadata_id < 8334) AND state > 17659

I get results with state like 2.00025078125 in my result set. I do NOT want to modify those. Any idea why this might be? Tried adding .0 behind 17659 but that didn't solve the problem.

[IOT7712]

Here be dragons.

Have you considered this app instead?

History Editor - edit, fix, and bulk-correct state history and statistics

I just published a new integration that implements a history editor that is able to edit state history, short-term and long-term statistics. With this, you’ll be able to correct those pesky wrong readings from that sensor that keep spoiling your graphs, repair your energy dashboard stats and bulk-correct wrong spikes. Deleting values from short-term or long-term statistics doesn’t actually deletes the stats rows, but “neutralizes” them, copying the value from the previously existing statistics…

As always, make plenty of verified backups that you can restore from should you accidentally corrupt your valuable data.

[WallyR (Wally)]

Are you sure the objects stored in the DB are numbers?

[SAFP (Peter)]

No, haven't seen it when I searched for solutions. I did find one possible solution after I posted, through dev tool -> statistics -> ramp icon. That did not correct everything, weirdly enough. I will look at that integration.

I make snapshots when I edit as HA runs in a VM

[SAFP (Peter)]

Oh wow. State is float in statistics and statistics_short_term but NOT in the states table! Character actually I must have implicitly assumed that would also be a float. That's something I wouldn't expect semantically. I just tried another solution to modify the data in the table but the problem is still not corrected so I will look at that integration mentioned.

[IOT7712]

A clean, controlled restart when you have finished your data modifications is recommended. Check the error logs afterwards for any hints of problems. Check again after 24 hours in case housekeeping/data backup gets heartburn.
Not till you are satisfied all is well should you even vaguely consider wiping any added backups you have made during this dangerous process.

[SAFP (Peter)]

I have created a work around:

UPDATE states
SET state = state - 17659.866986719
WHERE (metadata_id > 8328 AND metadata_id < 8334) AND CAST(state as DECIMAL) > 17659;

[WallyR (Wally)]

Good you found a way.

[SAFP (Peter)]

Very true. Now to continue this quest. I looked at the plugin but it is not widely used or so, not sure if I should add it so easily in terms of security. So I have modified my query as above, applied queries to statistics and statistics_short_term for sum and state columns after disabling the sensors by disabling the corresponding integration. Then directly restarted HA. It's weird, something seems to be adding back the deviation to the statistics. And it starts after reenabling the integration. So it must be something the integration does. Thanks for your help guys.