The very easy way is to delete ALL data, by editing configuraton.yaml (or maybe recorder.yaml) and adding:
recorder:
db_url: sqlite:////home/user/.homeassistant/new_database_file_name
Then restarting HA will create new database of the given name, removing all old historic data (no configuration will be lost - AFAIK)
To revert to the old data, delete the config line, restart HA - the old data should reappear.
The other option I have used is to run sqlite3 from the command line, and poke around.
Something like…
$ sqlite3 home-assistant_v2.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> select * from statistics_meta where statistic_id like 'sensor.gas%' ;
23|sensor.gas_consumption_today|recorder|kWh|0|1|
33|sensor.gas_consumption|recorder|m³|0|1|
...
which should help find the right data table. Then
sqlite> select * from statistics where metadata_id = 33 limit 10;
19972|2021-09-06 22:12:00.196918|33|2021-09-06 21:00:00.000000|||||0.220212499706642|0.220212499706642
20003|2021-09-06 23:12:00.209314|33|2021-09-06 22:00:00.000000|||||0.223174999706642|0.223174999706642
...
will help explore the data and identity what to delete.
To find out what the columns are we can use:
sqlite> pragma table_info(statistics) ...> ;
0|id|INTEGER|1||1
1|created|DATETIME|0||0
2|metadata_id|INTEGER|0||0
3|start|DATETIME|0||0
4|mean|FLOAT|0||0
5|min|FLOAT|0||0
6|max|FLOAT|0||0
7|last_reset|DATETIME|0||0
8|state|FLOAT|0||0
9|sum|FLOAT|0||0
The issue is HA seems to merge M3 and KWH data into the same table, and there in no easy way I can see of identifying just old M3 data.
So maybe something date based…
Use at your own risk, this could result in a corrupted HA database.
delete * from statistics where metadata_id = 33 and created < '2021/12/31'
The key to sucess will be finding the right table to delete from, and finding the right select statement to remove only the M3 data.
