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:

For more information on the statistics data, see also: HA history: more than 10 days? Long Time Statistic (LTS)

Edit data from wrong entries directly in the database

When integrating my water meter, I changed the unit from m³/h to liters and played wrong values into the database. The SQLite database can be read and edited via Linux board means with the command sqlite.

First, I connect to the Docker container of Home Assistant:

docker exec -it home-assistant /bin/bash

In the Docker container, sqlite can be installed and the database can be connected with the following command:

bash-5.1# apk add sqlite
bash-5.1# sqlite3 /config/home-assistant_v2.db

Connecting to the database allows any database queries to be executed. As an example, certain entries can be output with "select" or deleted with "delete":

sqlite> select * from states where entity_id = "sensor.water_value" and last_updated < "2022-12-22 22:56:48.421279";
sqlite> delete from states where entity_id = "sensor.water_value" and last_updated < "2022-12-22 22:56:48.421279";
sqlite> delete from states where entity_id = "sensor.water_state" and state > "890100";

See also: Home Assistant SQlite - Change statistics data

Change data directly in the SQLite database with VSCode

A little more convenient than Sqlite is the HA add-on: SQlite-Web or direct access to the host file system VSCode and the VSCode extension SQLite-Editor:

For editing, I did not use the Studio Code Server in Home Assistant, but installed VSCode locally and established a remote connection to the host on which the HA Docker container is running. For information on VSCode, see:"The 4 best tools for comparing 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

For the data in the "states" table, each entity has a metadata_id, which is stored in the states_meta table. The statistics tables use their own metadata_id, which can be found out as follows:

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;

Adding missing values

INSERT INTO statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) VALUES(NULL,NULL,NULL,NULL,NULL,NULL,1027.4,974.44,228,1704970800,1704970800,NULL);

I have put a converter online for compiling created_ts and start_ts: Convert Date: Unix Timestamp.

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

Old data from statistics_short_term is deleted regularly anyway, so it is usually not necessary to adjust this table, see: HA history: more than 10 days? Long Time Statistic (LTS).

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)

Worst case: Reconstruct statistics data

After Home Assistant Recorder writes the details of the last 10 days into the database and these are handled by the history integration, the statistics data of the last 10 days can be reconstructed from the details. By deleting the history tables in the database, Home Assistant will refill them with the data of the last 10 days at the next start.

Stop Home Assistant:

docker-compose down

Then using SQLite, delete the following tables:

Delete FROM "statistics_runs";
Delete FROM "statistics";
Delete FROM "statistics_short_term";

Start Home-Assistant again:

docker-compose up -d

The last statistics data will now be rebuilt in the background:

The table "statistics_runs" shows the progress:

The data contains initially only 2 entries per day ...

Transfer statistics data from another database

It is also possible to migrate data from another database, for example from a backup to the current database. A dump can be created for existing tables as follows: sqlite

user@server:/var/web$ sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /var/tmp/home-assistant_v2.db
sqlite> .output /var/tmp/export.sql
sqlite> .dump statistics
sqlite> .dump statistics_short_term
sqlite> .quit

I have edited the dump with an editor so that it can be imported into another database. Mainly to remove the id from the dump and to add the columns for the insert:

Regex-Search:

statistics VALUES\([0-9]{1,12},

Replace:

statistics (created,start,mean,min,max,last_reset,state,sum,metadata_id,created_ts,start_ts,last_reset_ts) VALUES(

The entries of the dump can then be imported into an existing database:

sqlite3
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open /var/tmp/home-assistant_v2.db
sqlite> .read /var/tmp/export.sql

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.