Huge jump in energy sum for no good reason

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.

Actually isn’t the Energy dashboard showing the wrong thing?

When I look at the sensor directly:

It is showing the correct usage as gathered from SmartThings:
image

But for whatever reason the Energy dashboard is displaying the incorrect sum:

I better not owe Epcor $10k this month… :wink:

I use dbbrowser for this, only downside is that it does not update after loading the db but otherwise much more friendly than CLI
DB Browser for SQLite (sqlitebrowser.org)

I have had similar issues with the cost sensor, with me it seems to originate from statistics_short_term that at some point (no clue when triggered or why) resets and then copies things to statistics.
The first graph does not use statistics for what I know, hence you will not see a difference, try checking ‘states’.
All in all, your energy did not have such a step so something went wrong in the calc and maybe it is as you say the long decimal
If you see a odd step in short_term too, raise a ticket with the HA platform on ‘energy’
New Issue · home-assistant/core (github.com)

Good idea about raising the issue. I still need to work out how to correct the values, though.