Home Assistant SQlite - Change statistics data
Originally, I had wrong values in the database every now and then when reading out the smart meter. I was able to correct the cause, but the wrong values do not look so good in the statistics. Only after I spent some time correcting the statistic data directly in the database, I discovered that wrong statistic values can be easily changed in Home-Assistant.
In the developer tools, in the tab "Statistics" the individual statistic values can be corrected:
Change data directly in SQLite database
First I looked for the wrong values in the SQLite database. Initially, I opened the VSCode extension SQLite-Editor to access the SQLite database:
Information about VSCode, see:"the 4 best tools to compare text files" and"PowerShell editors in comparison: ISE, Visual Studio Code".
With the VSCode extension SQLite3 Editor, the database files can be easily opened in the editor:
Using dropdown, the individual tables of the database can be selected and displayed:
In addition, a custom SQL query can be used for the query.
As an example, I wanted to take a closer look at the values of a certain sensor in a certain period of time:
SELECT * FROM "states" where entity_id = "sensor.meter01_active_energy_plus" and last_updated > "2023-01-14 06:00" and last_updated < "2023-01-14 12:00";
Queries are executed by pressing Shift + ENTER:
Individual values can be changed directly in the table:
Find out Metadata_id
Display of all statistic data of a sensor:
SELECT * FROM "statistics_short_term" where metadata_id = "86"
absolutely stop Home Assistant and create a backup of the database file "home-assistant_v2.db"!
Update of a single value via query
update "statistics_short_term" set state="95197.0" where id=744412;
Update of the column "SUM" in Statistics
Attention: Before execution the metadata_id must be adjusted, in the example 86:
Set all SUM values to NULL:
update "statistics" SET SUM = null where metadata_id = "86";
Fill the first value with 0:
update "statistics" SET SUM = 0 where rowid IN (SELECT rowid
FROM statistics
WHERE metadata_id = "86"
LIMIT 1);
Update
WITH t (id) AS (SELECT rowid FROM statistics WHERE SUM IS NULL AND metadata_id = "86") Update "statistics" SET SUM = ( (SELECT SUM FROM "statistics" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1) + (STATE - (SELECT state FROM "statistics" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1)) ) where rowid IN (SELECT id FROM t)
Table statistics_short_term
All SUM values to NULL:
update "statistics_short_term" SET SUM = null where metadata_id = "86";
The update in SQLite can be done using the rowid, which makes the update statement a bit more involved compared to SQL.
The initial value can then be populated as follows:
update "statistics_short_term" SET SUM = "2058.0" where rowid IN (SELECT rowid
FROM statistics_short_term
WHERE metadata_id = "86"
LIMIT 1);
The following query refills the remaining SUM values.
WITH t (id) AS (SELECT rowid
FROM statistics_short_term
WHERE SUM IS NULL
AND metadata_id = "86")
Update "statistics_short_term" SET SUM = (
(SELECT SUM FROM "statistics_short_term" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1) +
(STATE - (SELECT state FROM "statistics_short_term" where metadata_id = "86" and SUM != "null" ORDER BY "ID" DESC LIMIT 1))
)
where rowid IN (SELECT id FROM t)
Conclusion
Individual values can be changed very easily directly in Home-Assistant. The SQLite database used by Home-Assistant has some limitations compared to SQL or MySQL, but still the database can be accessed and edited directly. As an alternative to SQLite, Home-Assistant can also use MySQL as database, see: Home Assistant Database MySQL vs. SQLite.

{{percentage}} % positive
