Recorder long term statistics clean

Configuration
1

Tumbs up for long term statistics, great feature!

But i have a problem:

WARNING (Recorder) [homeassistant.components.sensor.recorder] The unit of sensor.ht_equalizer_import_energy_cost (NOK) does not match the unit of already compiled statistics (EUR). Generation of long term statistics will be suppressed unless the unit changes back to EUR

So i need to clean out the log term stats with EUR, how do i do that?

Recorder: Purge Entities and Recorder: Purge will not work on this issue.
Recorder is using mariadb in my case.

2

Up until now the fix has been to edit the database as described here

It fixed the same issue for me.
Just remember to shut down HA before editing

3

iam using mariadb not sqlite. there are no documentation on the structure of the db, editing it in the blind will almost certain result in lots of pain, my db is large, extremly large, it is almost 4 GB.

4

Have you updated to 2001.9.6 ?

I’m struggling to find the release notes but there was something in there about ‘confusing’ statistic sensor log entries.

Found it !

5

yes iam running 2021.9.6 and you are probably reffering to this PR: https://github.com/home-assistant/core/pull/56016 Yeah that is not it.

6

Bugger

Apparently a post needs to be at least 10 characters :roll_eyes:

7

yes, currently there are no ways to clean out long term statistics, pretty weird i have to say, but i guess it will come soon enough

8

That’s because there is no DB structure. It’s basically used as a flat file. Most of the information recorded for a state change, for example, is redundant entity name and attributes. If you open it up and take a look you’ll probably be shocked at how terrible it is.

That’s why we have 4+ GB databases to record 400K worth of data.

9

are there any solution for this problem now?

10

with SQLite Web add-on, I have changed mine from USD to EUR :

UPDATE "statistics_meta" SET unit_of_measurement='EUR' WHERE statistic_id = 'sensor.xxx_cost'

‘sensor.xxx_cost’ being the totalizing cost sensor you’d like to move to another currency.
you can search for the “*cost” entities you may have for instance with below statement:

SELECT *
FROM "statistics_meta" WHERE statistic_id LIKE "%_cost"

or via configuration menu → entities