I like the Energy dashboard. It’s fun, and the graphs give me the warm fuzzies.
I discovered my energy sum values suddenly went incorrect, so I went digging.
Step 1 - Install sqlite3
I am running Home Assistant Container with the configuration mapped to /srv/homeassistant/config. I needed to install sqlite3 in order to access the database file in that directory.
Simple enough, let’s install the package:
$ sudo apt install sqlite3
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies:
sqlite3 : Depends: libsqlite3-0 (= 3.34.1-3) but 3.38.5-1 is to be installed
E: Unable to correct problems, you have held broken packages.
Ahh. Not so easy. But there is a simple fix!
$ sudo apt upgrade
$ sudo apt install libsqlite3-0=3.34.1-3
Now I can install the package I need:
$ sudo apt install sqlite3
Step 2 - Access the database
With sqlite3 installed I can now get to the database:
$ sqlite3 /srv/homeassistant/config/home-assistant_v2.db
Some settings to make things easier to use:
sqlite> .header on
sqlite> .mode column
sqlite> .databases
main: /srv/homeassistant/config/home-assistant_v2.db r/o
The command needed to find the entity id for my energy meter:
sqlite> select * from statistics_meta where statistic_id = "sensor.garage_energy_energy_meter"
...> ;
id statistic_id source unit_of_measurement has_mean has_sum name
-- --------------------------------- -------- ------------------- -------- ------- ----
46 sensor.garage_energy_energy_meter recorder kWh 0 1
Now to find what happened:
sqlite> select * from statistics where metadata_id=46 and created > '2022-12-03 07:00:00' order by created asc;
id created start mean min max last_reset state sum metadata_id
------ -------------------------- -------------------------- ---- --- --- ---------- ------ ---------------- -----------
140072 2022-12-03 07:00:10.243145 2022-12-03 06:00:00.000000 645.34 7552.9 46
140130 2022-12-03 08:00:10.219535 2022-12-03 07:00:00.000000 645.9 8199.3 46
140188 2022-12-03 09:01:00.378731 2022-12-03 08:00:00.000000 646.58 8199.98 46
140246 2022-12-03 10:00:10.218137 2022-12-03 09:00:00.000000 647.19 8200.59 46
140304 2022-12-03 11:00:10.208478 2022-12-03 10:00:00.000000 647.93 8201.33 46
140362 2022-12-03 12:00:10.324260 2022-12-03 11:00:00.000000 649.32 8202.72 46
140420 2022-12-03 13:00:10.337535 2022-12-03 12:00:00.000000 652.12 8205.52 46
140478 2022-12-03 14:00:10.314619 2022-12-03 13:00:00.000000 653.0 8206.4 46
140536 2022-12-03 15:47:10.215410 2022-12-03 14:00:00.000000 653.38 8206.78 46
140594 2022-12-03 16:00:10.103700 2022-12-03 15:00:00.000000 655.69 8209.08999999999 46
140652 2022-12-03 17:00:10.138749 2022-12-03 16:00:00.000000 656.85 73887.25 46
140710 2022-12-03 18:00:10.120618 2022-12-03 17:00:00.000000 660.5 73890.9 46
140768 2022-12-03 19:00:10.111903 2022-12-03 18:00:00.000000 661.41 73891.81 46
140826 2022-12-03 20:00:10.136406 2022-12-03 19:00:00.000000 662.16 73892.56 46
140884 2022-12-03 21:00:10.141393 2022-12-03 20:00:00.000000 662.88 73893.2800000001 46
140943 2022-12-03 22:00:10.113877 2022-12-03 21:00:00.000000 663.52 73893.9200000001 46
141002 2022-12-03 23:00:10.111766 2022-12-03 22:00:00.000000 664.34 73894.7400000001 46
141061 2022-12-04 00:00:10.121536 2022-12-03 23:00:00.000000 666.04 73896.4400000001 46
141120 2022-12-04 01:00:10.141040 2022-12-04 00:00:00.000000 668.74 73899.1400000001 46
141178 2022-12-04 02:00:10.129024 2022-12-04 01:00:00.000000 669.7 73900.1000000001 46
141236 2022-12-04 03:00:10.139516 2022-12-04 02:00:00.000000 670.45 73900.8500000001 46
141294 2022-12-04 04:00:10.142805 2022-12-04 03:00:00.000000 671.13 73901.5300000001 46
Entry id 140594 has the sum at 8209.08999999999, then id 140652 takes it to 73887.25. That’s over 65678.16000000001 when it should have been 8865.94.
Does anyone know what might have caused this?
It seems like the excess float values caused the sum to flip out.
