Interpolation of missing Energy Meter readings - smooth statistics

Configuration
,
#1

I have a problem with gaps in the statistics. My energy meter (total consumption) had reading errors for several days and the meter reading was entered in the statistics with the old status.

Now that the meter reading is being read correctly again, the interim consumption is added to the first day with plausible values.

image

Looking in the statistics table you see that there are rows on Jan 2nd having the old (not updated) sensor value as state.

image
image790×656 198 KB

I know that it is possible to correct one particular datapoint. But how can I smooth out the spike on Jan 3rd and have the values of the missing gap interpolated?
I found some topics discussing SQL statements but non of them told me how to do the maths to update the existing datasets with the right calculated values.

#2

Mar or may not be easier:

Go to Developer Tools → Statistics, find your sensor, click the ramp icon to the right. You can edit the readings (one at a time!) there.

#4

I have several of those hiccups between october and januar resulting in multi-day-gaps.
Editing each wrong dataset within each of the gaps would take ages via UI I guss.

#5

Yep. How long is it going to take you to learn the SQL and table schemas required to do it the other way though?

That’s the deciding factor between the two methods.

Just throwing it out there as an option for you to consider if you don’t find the help you need.

1 Like
#6

I decided to fill the erroneous data points with calculated mean values by writing the calculated states and sums directly into the statistics table.
Unfortunately, this already amounted to 86 rows for two sensors over two days.

It would be very useful if Home Assistant would offer the option of distributing a delayed update of a sensor retroactively over a definable period of time via UI.

Or as ChatGPT would understand:
My sensor updated on the third day with the entire consuption of the last three days. Please distribute the consumption over day one and two linearly. :wink: