I found similar problem and took a similar path. I’m documenting here the result so you can use my store procedure.
- There is NO NEED to delete any record in the database. Only the
sumfield will be updated. - It should be safe to execute the procedure multiple times as it will fix only the values that cause negative values. If the sum is correct, the procedure will not update anything.
- It is not needed to provide a date, the entire table will be considered.
- It is recommended (I would say, it is mandatory) to run the procedures with Home Assistant NOT RUNNING. Because I suspect HA maintains internal counters or caches over the database records and next inserts will be wrong (and will produce more negative numbers)
Now the code
CREATE DEFINER=`hass`@`%` PROCEDURE `fix_statistics_sum`(IN `entity_id` VARCHAR(255))
BEGIN
DECLARE curr_id INT;
DECLARE prev_sum FLOAT DEFAULT -1;
DECLARE curr_sum FLOAT;
DECLARE sum_diff FLOAT;
DECLARE acum_error FLOAT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT id, sum FROM statistics s WHERE s.metadata_id = (SELECT id FROM statistics_meta sm WHERE sm.statistic_id = entity_id) ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO curr_id, curr_sum;
IF done THEN
LEAVE read_loop;
END IF;
IF prev_sum <> -1 THEN
SET sum_diff = curr_sum - prev_sum;
IF sum_diff < 0.0 THEN
SET acum_error = acum_error - sum_diff;
END IF;
UPDATE statistics s SET s.sum = (curr_sum + acum_error) WHERE s.id = curr_id;
END IF;
SET prev_sum = curr_sum;
END LOOP;
select acum_error, prev_sum, curr_sum ;
CLOSE cur1;
END
CREATE DEFINER=`hass`@`%` PROCEDURE `fix_statistics_short_term_sum`(IN `entity_id` VARCHAR(255))
BEGIN
DECLARE curr_id INT;
DECLARE prev_sum FLOAT DEFAULT -1;
DECLARE curr_sum FLOAT;
DECLARE sum_diff FLOAT;
DECLARE acum_error FLOAT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT id, sum FROM statistics_short_term s WHERE s.metadata_id = (SELECT id FROM statistics_meta sm WHERE sm.statistic_id = entity_id) ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO curr_id, curr_sum;
IF done THEN
LEAVE read_loop;
END IF;
IF prev_sum <> -1 THEN
SET sum_diff = curr_sum - prev_sum;
IF sum_diff < 0.0 THEN
SET acum_error = acum_error - sum_diff;
END IF;
UPDATE statistics_short_term s SET s.sum = (curr_sum + acum_error) WHERE s.id = curr_id;
END IF;
SET prev_sum = curr_sum;
END LOOP;
select acum_error, prev_sum, curr_sum ;
CLOSE cur1;
END
Regards
Ignacio
