IotaWatt Integration, Spikes in Energy Page

There are a lot of topics similar to this on the forums, but none of them I have found have a solution to the core problem. I have been bashing my head against the database for a couple of days now, and hopefully what I lay out here will help us come to a solution that works for more people.

First the setup. I have an IotaWatt installed in my house. I have created the HA integration and connected to it fine. When I get a spike, it appears that all the meters get a spike at the same time, but the history graph for individual meters doesn’t show any issues.

For the purpose of this discussion, I am only going to show the data from “OfficeOutletsAndLights” and zeroing in on the end of the day. There is a spike from 9-10PM and from 11PM-12AM. Here is the graph from the Energy page:

And here is the history graph for that sensor:

As you can see, the sensor history isn’t showing any gaps or blips, and the WH graph just keeps trucking along at a steady pace until midnight when it resets (the bottom point is 2WH, it never hits 0).

OK, now the database review. The meta ID for this sensor is 20/21.

As far as I can see, the Power (W) data from 20 isn’t used in creating the energy graphs, but here is the table with the 2 times highlighted for reference:

This is the statistics table for 21 where you can clearly see the sum column jumped even though the state column is pretty even:

in the statistics_short_term table for 21, the sum column jumps even though the state column looks right.

If we just look at the 4 highlighted squares under state and sum:
image

The way the math should work is every sum square = previous sum + current state - previous state. The cell with 18653 is instead = previous sum + current state. It didn’t subtract the previous state. It isn’t near the reset time either.

It looks like every entry in the statistics table has a matching state/sum pair in the statistics_short_term, so I don’t know if there are different computations, or just when it is time to put a point in the statistics table, it just grabs the latest values from the statistics_short_term table. This would make since because the created_ts timestamps for the statistics_short_term table are right before the statistics table.

To combat this problem, I have written a C# application that can rebuild the tables by recomputing the sum column for these 2 tables. I can then refresh the energy graphs and they look correct:

Does anyone have a solution to this problem? Any ideas for me to explore in my database as to what could be causing this issue?

I will post a follow-up after the next spike…

Thanks!
John Vickers

One other note, I was running the build from 2024.7.?. I had another spike about an hour after my previous post.

This morning I scrubbed the spike and updated to 2025.1.2. Still getting errors after the update however…

For others having this issue, I was able to do some SQL FU with ChatGTP and came up with this query to fix summation column for any statistics sensors:

-- Process for statistics table
WITH MetadataIDs AS (
    SELECT id AS metadata_id
    FROM statistics_meta
    WHERE has_sum = 1
),
StateDeltas AS (
    SELECT
        s.id,
        s.metadata_id,
        s.created_ts,
        s.start_ts,
        s.last_reset_ts,
        s.state,
        CASE
            WHEN s.state >= LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            THEN s.state - LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            ELSE s.state
        END AS power_used
    FROM statistics s
    JOIN MetadataIDs m
    ON s.metadata_id = m.metadata_id
),
CumulativeSum AS (
    SELECT
        id,
        metadata_id,
        SUM(power_used) OVER (PARTITION BY metadata_id ORDER BY start_ts) AS cumulative_sum
    FROM StateDeltas
),
MismatchedSums AS (
    SELECT
        s.id,
        c.cumulative_sum
    FROM statistics s
    JOIN CumulativeSum c
    ON s.id = c.id
    WHERE s.sum != c.cumulative_sum OR s.sum IS NULL
)
UPDATE statistics
SET sum = (
    SELECT cumulative_sum
    FROM MismatchedSums
    WHERE MismatchedSums.id = statistics.id
)
WHERE id IN (SELECT id FROM MismatchedSums);

-- Process for statistics_short_term table
WITH MetadataIDsShort AS (
    SELECT id AS metadata_id
    FROM statistics_meta
    WHERE has_sum = 1
),
StateDeltasShort AS (
    SELECT
        s.id,
        s.metadata_id,
        s.created_ts,
        s.start_ts,
        s.last_reset_ts,
        s.state,
        CASE
            WHEN s.state >= LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            THEN s.state - LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            ELSE s.state
        END AS power_used
    FROM statistics_short_term s
    JOIN MetadataIDsShort m
    ON s.metadata_id = m.metadata_id
),
CumulativeSumShort AS (
    SELECT
        id,
        metadata_id,
        SUM(power_used) OVER (PARTITION BY metadata_id ORDER BY start_ts) AS cumulative_sum
    FROM StateDeltasShort
),
MismatchedSumsShort AS (
    SELECT
        s.id,
        c.cumulative_sum
    FROM statistics_short_term s
    JOIN CumulativeSumShort c
    ON s.id = c.id
    WHERE s.sum != c.cumulative_sum OR s.sum IS NULL
)
UPDATE statistics_short_term
SET sum = (
    SELECT cumulative_sum
    FROM MismatchedSumsShort
    WHERE MismatchedSumsShort.id = statistics_short_term.id
)
WHERE id IN (SELECT id FROM MismatchedSumsShort);

Does anyone know of a way I could have this run as a task at some interval?

Hopefully this will help someone else fix their data!

Thanks,
John Vickers

Well this is a bit of a hacky solution, but it will hold me over for now. Hopefully this will help someone else!

In your configuration.yaml, add this:

shell_command:
  install_sqlite3: apk add --no-cache sqlite
  run_sqlite_query: sqlite3 /config/home-assistant_v2.db < /config/FixStatisticsSummation.sql

Save, then restart HA. Important note, I am running in a docker container which does not come with sqlite installed. So every time I update the docker container I need to install sqlite to be able to run my query.

Go to Developer Tools, then Actions. Search for Shell, and you should see “Shell Command: install_sqlite3”, click that then “Perform Action”.

Now you have access to running sqlite commands from HA.

Create a file called “FixStatisticsSummation.sql” in the same directory as your configuration.yaml. This is the contents:

-- Attach a new database file
ATTACH DATABASE 'FixStatisticsSummation.db' AS fix_db;

-- Create table in the new database if it doesn't exist
CREATE TABLE IF NOT EXISTS fix_db.fix_statistics_counts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_ts INTEGER NOT NULL,
    updated_table TEXT NOT NULL,
    updated_rows INTEGER NOT NULL
);





-- Process for statistics table
WITH MetadataIDs AS (
    SELECT id AS metadata_id
    FROM statistics_meta
    WHERE has_sum = 1
),
StateDeltas AS (
    SELECT
        s.id,
        s.metadata_id,
        s.created_ts,
        s.start_ts,
        s.last_reset_ts,
        s.state,
        CASE
            WHEN s.state >= LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            THEN s.state - LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            ELSE s.state
        END AS power_used
    FROM statistics s
    JOIN MetadataIDs m
    ON s.metadata_id = m.metadata_id
),
CumulativeSum AS (
    SELECT
        id,
        metadata_id,
        SUM(power_used) OVER (PARTITION BY metadata_id ORDER BY start_ts) AS cumulative_sum
    FROM StateDeltas
),
MismatchedSums AS (
    SELECT
        s.id,
        c.cumulative_sum
    FROM statistics s
    JOIN CumulativeSum c
    ON s.id = c.id
    WHERE s.sum != c.cumulative_sum OR s.sum IS NULL
)
UPDATE statistics
SET sum = (
    SELECT cumulative_sum
    FROM MismatchedSums
    WHERE MismatchedSums.id = statistics.id
)
WHERE id IN (SELECT id FROM MismatchedSums);

-- Log the update operation
INSERT INTO fix_db.fix_statistics_counts (created_ts, updated_table, updated_rows)
SELECT 
    strftime('%s', 'now'),  -- Current timestamp (in seconds)
    'statistics',           -- Updated table name
    changes()               -- Number of rows updated
WHERE TRUE;      -- Always Log
-- WHERE changes() > 0;      -- Only log if rows were updated





-- Process for statistics_short_term table
WITH MetadataIDs AS (
    SELECT id AS metadata_id
    FROM statistics_meta
    WHERE has_sum = 1
),
StateDeltas AS (
    SELECT
        s.id,
        s.metadata_id,
        s.created_ts,
        s.start_ts,
        s.last_reset_ts,
        s.state,
        CASE
            WHEN s.state >= LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            THEN s.state - LAG(s.state) OVER (PARTITION BY s.metadata_id ORDER BY s.start_ts)
            ELSE s.state
        END AS power_used
    FROM statistics_short_term s
    JOIN MetadataIDs m
    ON s.metadata_id = m.metadata_id
),
CumulativeSum AS (
    SELECT
        id,
        metadata_id,
        SUM(power_used) OVER (PARTITION BY metadata_id ORDER BY start_ts) AS cumulative_sum
    FROM StateDeltas
),
MismatchedSums AS (
    SELECT
        s.id,
        c.cumulative_sum
    FROM statistics_short_term s
    JOIN CumulativeSum c
    ON s.id = c.id
    WHERE s.sum != c.cumulative_sum OR s.sum IS NULL
)
UPDATE statistics_short_term
SET sum = (
    SELECT cumulative_sum
    FROM MismatchedSums
    WHERE MismatchedSums.id = statistics_short_term.id
)
WHERE id IN (SELECT id FROM MismatchedSums);

-- Log the update operation
INSERT INTO fix_db.fix_statistics_counts (created_ts, updated_table, updated_rows)
SELECT 
    strftime('%s', 'now'),  -- Current timestamp (in seconds)
    'statistics_short_term',           -- Updated table name
    changes()               -- Number of rows updated
WHERE TRUE;      -- Always Log
-- WHERE changes() > 0;      -- Only log if rows were updated






-- Commit the transaction
COMMIT;

-- Detach the new database file
DETACH DATABASE fix_db;

This query will recreate the sum column in the statistics and statistics_short_term tables, and create/update a tracking database to add a row every time it runs on each table to show how many records it had to fix.

Now create and automation to run this every hour 5 minutes after the hour:

  1. Go to Automations
  2. Click Create Automation
  3. Click Create New Automation
  4. Click Add Trigger
  5. Select Time Pattern
  6. Enter 5 in Minutes Field
  7. Click Add Action
  8. Select “Shell Command ‘shell_command.run_sqlite_query’”
  9. Click Save
  10. Give it a name like “FixStatisticsSummation”

You can now go back to automations, click the ellipses on the new automation, and run the action to trigger it manually. Once you run it, you should see a new db file in the same folder as configuration.yaml. If you open this in a database viewer, you should see 2 lines in the only table, one for statistics and one for statistics_short_term, each row will have an epoch for time run and a number of rows in the table it had to fix.

If you had an issue and run this back to back 2x, you should have 2 rows with the 2 tables with some number of fixed rows, then 2 rows with the 2 tables with 0 fixes. That tells you it is working. Every hour at 5 minutes past, you should get 2 new entries showing you how many it had to fix.

What I have found using this to scrub my database is that even if you are not seeing huge power spikes like in my first post in this thread, HA is accumulating summation errors all the time and I have no idea why. This will fix them as they go so you don’t end up with useless statistics.