I’ve tryed to change hte value but sum values after this value was not recalculated.
If you are on SQLite then it might be a bit complicated. Here are example SQLs that need manual adjustments - entity_id, date range and start value for recalculation in the last query:
-- database: c:\tmp\home-assistant_v2.db
--backup
CREATE TABLE IF NOT EXISTS statistics_backup AS
SELECT * FROM statistics;
-- Restore from backup
DROP TABLE IF EXISTS statistics;
CREATE TABLE IF NOT EXISTS statistics AS
SELECT * FROM statistics_backup;
-- Use this query to see results
-- Select date range is 1 hour before the update range
-- In the first row you will see the initial 'sum' value and can use it instead of 1000 in the step 2.
SELECT id, state, sum, datetime(start_ts, 'unixepoch') as readable_time
FROM "statistics"
WHERE
metadata_id = 172
AND datetime(start_ts, 'unixepoch') >= '2024-09-30 22:00:00'
AND datetime(start_ts, 'unixepoch') <= '2024-10-17 12:59:59'
ORDER BY start_ts;
UPDATE statistics
SET sum = 0
WHERE
metadata_id = 172
AND datetime(start_ts, 'unixepoch') >= '2024-10-01 00:00:00'
AND datetime(start_ts, 'unixepoch') <= '2024-10-17 12:59:59';
-- Step 1: Calculate sum deltas
WITH ordered_statistics AS (
SELECT id, state, start_ts,
LAG(state, 1, 0) OVER (ORDER BY id) AS prev_state,
LAG(sum, 1, 0) OVER (ORDER BY id) AS prev_sum
FROM statistics
WHERE metadata_id = 172
AND datetime(start_ts, 'unixepoch') >= '2024-10-01 00:00:00'
AND datetime(start_ts, 'unixepoch') <= '2024-10-19 23:59:59'
)
UPDATE statistics
SET sum = ROUND(
(SELECT CASE
-- If current state is lower than previous (reset occurred)
WHEN ordered_statistics.state < ordered_statistics.prev_state THEN ordered_statistics.prev_sum + ordered_statistics.state
-- Normal cumulative sum calculation
ELSE ordered_statistics.prev_sum + (ordered_statistics.state - ordered_statistics.prev_state)
END
FROM ordered_statistics
WHERE ordered_statistics.id = statistics.id), 3)
WHERE metadata_id = 172
AND datetime(start_ts, 'unixepoch') >= '2024-10-01 00:00:00'
AND datetime(start_ts, 'unixepoch') <= '2024-10-19 23:59:59';
-- Step 2: Convert sum deltas into incremental value
WITH ordered_statistics AS (
SELECT id, state, created_ts,
LAG(state, 1, 2230.060000000319) OVER (ORDER BY id) AS prev_state,
LAG(sum, 1, 2230.060000000319) OVER (ORDER BY id) AS prev_sum -- Start from the sum of the first row outside the range
FROM statistics
WHERE metadata_id = 172
AND datetime(created_ts, 'unixepoch') >= '2024-10-01 00:00:00'
AND datetime(created_ts, 'unixepoch') <= '2024-10-19 23:59:59'
),
deltas AS (
SELECT id,
CASE
-- If the current state is less than the previous state, it indicates a reset
WHEN state < prev_state THEN state
-- Otherwise, calculate the delta as the difference between the current and previous state
ELSE state - prev_state
END AS state_delta,
prev_sum
FROM ordered_statistics
),
cumulative AS (
SELECT id,
-- Only add the hardcoded value to the first record in the range
CASE
WHEN id = (SELECT MIN(id) FROM ordered_statistics) THEN state_delta + prev_sum
ELSE SUM(state_delta) OVER (ORDER BY id) + 2230.060000000319
END AS cumulative_sum
FROM deltas
)
UPDATE statistics
SET sum = ROUND(cumulative.cumulative_sum, 3) -- Update the sum column with the calculated cumulative sum
FROM cumulative
WHERE statistics.id = cumulative.id
AND metadata_id = 172
AND datetime(created_ts, 'unixepoch') >= '2024-10-01 00:00:00'
AND datetime(created_ts, 'unixepoch') <= '2024-10-19 23:59:59';
In his SQLs following values are hardcoded:
Replace this hardcoded valus in the SQLs:
2230.060000000319 - is sum value that should be used as a start
2024-10-01 00:00:00 - start date time
2024-10-19 23:59:59 - end date time, can be in future
172 - metadate_id of your sensor.
