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 edited 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)

Editing data from incorrect entries directly in the database

When integrating my water meter, I changed the unit from m³/h to liters and entered incorrect values in the database. There are several options for direct database access, for example SQLite Web or SQLite3 in VSCode, see: ha-sqlite#vscode.

Finding out the 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 "states_meta" table, the id is the metadata_id value in the statistics tables.

Display of all statistical data of a sensor using the metadata_id:

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

The id can also be determined with the following query:

SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus'

To use the name of the entity instead of the metadata_id to display the data, the two queries can be combined:

SELECT * FROM "statistics_short_term" where metadata_id = (SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.meter01_reactive_energy_plus')

and so that created_ts and start_ts are also output as date and time, the query can be extended accordingly:

SELECT datetime(start_ts, 'unixepoch', 'localtime') as start_ts_readable, datetime(created_ts, 'unixepoch', 'localtime') as created_ts_readable, * FROM "statistics" 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.

Alternatively, the values of a sensor can also be deleted beforehand and recreated on the basis of another sensor

In the following example, I have deleted the statistical data of the entity: sensor.fronius_mppt_module_2_lifetime_energy and filled it with the statistical data of the sensor sensor.byd_total_charging.

Tabelle: statistics_meta:

Attention: Values 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.