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:

Find out Metadata_id

Display of all statistic data of a sensor:

SELECT * FROM "statistics_short_term" where metadata_id = "86"

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.