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:

#7

I just stumbled across the same problem.

I recently got my PV system ready, but due to a hiccup in the network, HA was not able to fetch the energy meter value for a couple of hours.

Once it catches up, it valued the entire counter increase to the current point in time instantly.

Yes, I could change the “one problematic value” but the value itself is good. It’s the real counter at that specific time. Instead I obviously would have to insert missing values or to get HA to at least “assume steady progress between two successful readings”.

2023-05-18_16-25
No reading during the night, as the device shuts off, but in fact there IS no progress during that time.

2023-05-18_16-25_1
2023-05-18_16-25_1794Ă—452 10.2 KB

But obviously I got a solar blast before 15:00h.

Is there any means to get this straight? For a strictly increasing counter a linear guess might be the better assumption than the current “all at once”. Losing track of regular updates is something that might happen for various reasons.

1 Like