Energy spikes

Configuration Z-Wave
1

I have seen some posts of this problem, but none of the solutions seems to fit my setup.

I recently switched from Indigo to Home Assistant. I didn’t want to reset my the accumulated values in my Aeotec energy meter. I think this might be the culprit.

The first image shows the start of the series from the statistics table. This was seven days ago. The second image shows what happened today.

image
image964×952 59.4 KB

It seems to be alright to start with an accumulated value in state. Today, HA suddenly descided to add the ackumulated state to the sum in some manner. The new sum has two more digits than the state value, I’m guessing that some kind of string concatenation took place.

Q1. What is the best way to clean the data? I saw a post where someone simply deleted rows with too high sums, but that would break the sum series. I’m guessing that the correct way would be to simply recalculate the sum column fom start?

Q2. How do I prevent this from happening again? Should I whipe the statistics completely and reset the accumulated value in the device?

Thanks
//Michael

2

Q1: You can go into Developer Tools > Statistics tab > search the device that recorded a bad reading, click the Adjust sum button and go to the timestamp.

image
image685×919 24.8 KB

Click the bad entry and adjust it to what it should (approx) be.

Q2: I think it just happens sometimes, but no idea if there’s an actual fix.

3

Thanks.
I wrote an SQL statement to recalculate all bad sums. This made the energy graphs look as they should. Unfortunately, the cost is still bonkers.

I’m getting hourly kWh rates from Tibber fed into HA. Unfortunately It seems like the energy module just multiplies the rate with the consumption and stores the value another statistics series. The cost series is incorrect due to the glitch in the energy series. I cannot see any way to fix it since the actual hourly rate is not stored.

Is it possible get the recorder to store the hourly rate in the database as well? In that case I can recalculate the cost if/when this happens again.

4

In my case the state-values were correct. Only the sum column was incorrect. I’ll post my SQLs here if anyone gets into the same situation.

N.B: I’m using Postgres. I’m not sure if SqlLite implements the lag() statement. I’ve seen solutions using a cursor. The below code does the same thing, but “set based”.

N.B.2: Please dry-run the SQL within a rollbacked transaction before running it for real to ensure that your problem gets solved and no unpleasant surprises turns up. The code works for me, no guarantees for your use case…


--Recalculate sum column in statiatics
WITH delta_calc AS (
  SELECT 
    id, 
    state - lag(state) over (order by id) as delta
  FROM statistics
  WHERE metadata_id = 57 -- << insert your ID
), 
sum_calc AS (
  SELECT
    delta_calc.id, 
    COALESCE(sum(delta) over (ORDER BY delta_calc.id), 0) as new_sum, 
    statistics.sum as old_sum
  FROM delta_calc
  JOIN statistics ON statistics.id = delta_calc.id
)
UPDATE statistics 
SET sum = new_sum 
FROM sum_calc
WHERE sum_calc.id = statistics.id;


--Recalculate sum column in statiatics_short_term
WITH delta_calc AS (
  SELECT 
    id, 
    state - lag(state) over (order by id) as delta
  FROM statistics_short_term
  WHERE metadata_id = 57 -- << insert your ID
), 
sum_calc AS (
  SELECT
    delta_calc.id, 
    COALESCE(sum(delta) over (ORDER BY delta_calc.id), 0) as new_sum, 
    statistics_short_term.sum as old_sum
  FROM delta_calc
  JOIN statistics_short_term ON statistics_short_term.id = delta_calc.id
)
UPDATE statistics_short_term
SET sum = new_sum 
FROM sum_calc
WHERE sum_calc.id = statistics_short_term.id;
1 Like