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

The connection to the database allows you to execute any database queries,see also: Home Assistant SQlite - Change statistical 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.

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:

The value "statistic_id" is the name of the sensor in the table "states_meta", the id is the value metadata_id in the statistics tables.

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

Transferring statistics from one device to another

After replacing the inverter in my balcony power plant, I didn't want to lose the system's statistical data and wanted to continue using it with the new inverter. Only the ID of the sensor is used for mapping the statistics data:

If statistic_id, here "sensor.balkonkw_total_production" is exchanged with another sensor name: in the following example sensor.balkon_yieldtotal, the statistical data can be transferred: 

By renaming the statistic_id, all values of an existing sensor can be transferred to another one:

Als zweites Bespiel hatte die ursprüngliche Firmware-Version des Fronius Gen24-Wechselrichters ein Problem mit dem Zähler mit den Ladedaten für meinen BYD-Speicher.

Alternativ können die Werte eines Sensors auch zuvor gelöscht und auf Basis eines anderen Sensors neu erstellt werden

In folgendem Beispiel habe ich die Statistikdaten der Entität: sensor.fronius_mppt_module_2_lifetime_energy gelöscht und mit den Statistikdaten des Sensors sensor.byd_total_charging befüllt.

Tabelle: statistics_meta:

Achtung: Werte in kWh ...

Attention: Values in Wh

DELETE FROM statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');

WITH target_sensor_statistics_meta_id AS (
    SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy'
), source_sensor_statistics_meta_id AS (
    SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.byd_total_charging'
)
INSERT INTO statistics (metadata_id, state, sum, created, start, mean, min, max, last_reset, created_ts, start_ts)
SELECT (SELECT id FROM target_sensor_statistics_meta_id), state * 1000, sum * 1000, created, start, mean, min, max, last_reset, created_ts, start_ts
FROM statistics
WHERE metadata_id = (SELECT id FROM source_sensor_statistics_meta_id);

SELECT * FROM statistics where metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.fronius_mppt_module_2_lifetime_energy');

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.