This was a great help for me, thanks.
My cost calculation started to accelerate to ludicrous amounts after I migrated my data to MariaDB.
Just be cautious - the “energy cost” (not “energy”) statistics can have the “last_reset” column filled, and on the rows that this datetime changes from the previous row, the “prev_state” must not be subtracted from “state”. So have a look at the rows for your metadata_id, and see if it has “last_reset” set.
In this case the procedure must be altered to take care of this. I did this in the ugliest way possible because I didn’t want to spend more time on it, here it is in case you want to improve it:
CREATE DEFINER=`homeassistant`@`%` PROCEDURE `fix_short_term`(IN `metadata` INT, IN `from_date` DATETIME)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE curr_id INT;
DECLARE reset_date DATETIME;
DECLARE prev_reset_date DATETIME;
DECLARE cur1 CURSOR FOR SELECT id, last_reset FROM statistics_short_term where metadata_id = metadata and created >= from_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
FETCH cur1 INTO curr_id, reset_date;
SET prev_reset_date = reset_date;
read_loop: LOOP
IF done THEN
LEAVE read_loop;
END IF;
IF reset_date is null OR reset_date = prev_reset_date THEN
update statistics_short_term as s1 inner join (
select s1.id, s2.sum as prev_sum, s2.state as prev_state from statistics_short_term as s1
inner join statistics_short_term as s2 on s2.id = (
select id from statistics_short_term s2 where s1.metadata_id = s2.metadata_id and s2.created >= from_date and s2.id < curr_id order by id desc limit 1)
where s1.id = curr_id)
as prev_table on s1.id = prev_table.id
set s1.sum = round(prev_sum + state - prev_state, 3);
ELSE
update statistics_short_term as s1 inner join (
select s1.id, s2.sum as prev_sum, s2.state as prev_state from statistics_short_term as s1
inner join statistics_short_term as s2 on s2.id = (
select id from statistics_short_term s2 where s1.metadata_id = s2.metadata_id and s2.created >= from_date and s2.id < curr_id order by id desc limit 1)
where s1.id = curr_id)
as prev_table on s1.id = prev_table.id
set s1.sum = round(prev_sum + state, 3);
SET prev_reset_date = reset_date;
END IF;
FETCH cur1 INTO curr_id, reset_date;
END LOOP;
CLOSE cur1;
END
