Meter value used instead of difference when start of date range falls in a gap in the statistics

[rrozema]

Checklist:

• [x ] I updated to the latest version available
• [x ] I cleared the cache of my browser

Release with the issue:
Sankey Chart Card: 1.14.0
Home Assistant 2023.6.3
Supervisor 2023.06.4
Operating System 10.3
Frontend 20230608.0 - latest

Last working release (if known):
N/A

Browser and Operating System:
Both in Chrome on windows, in HA windows app and in Android app. It's a data collection issue, not a display issue so probably not relevant anyway.

Description of problem:
Incorrect data is shown in the chart for some sensors at some days/weeks/months.
If the statistics for a sensor have a gap -because f.e. the sensor was offline at some time- and the start of the selected date range is within this gap (i.e. there is no data available for the start of the selected period) but data is available before the end of the selected period, the running total will be too high. Instead of the running total of usage, the value of the meter is read from the first data point that is available in the selected period. The effect is best seen on meters that have not been reset recently as then the incorrectly added value will be high.

The running total should include only differences between 2 measurements, not the value of the meter itself.

For an example of the error, see this picture of June 22nd's energy usage overview. The encircled 'Keuken: Spots: Verbruik' had been offline from 21-06-2023 13:50:00.000 due to a z-wave connection issue, which I fixed on June 22-06-2023 08:05:00.000. i.e. No statistics were stored for this device in that period. The usage listed for June 22nd for this devices matches exactly the 3.5kWh that is the total usage for this device all time. When in the date picker I select June 21st, usage is listed as 0kWh, when I select June 23rd usage is 0kWh, and even when I select the week of June 19th to June 25th, i.e. the week that has June 22nd in it, the usage is listed as 0kWH. Only when the start of the range falls into a gap, the running total is incorrect.
A document containing the queries I used to verify the contents of the statistics table for this device, plus the resulting output can be found in my google drive.

Javascript errors shown in the web inspector (if applicable):
N/A

Additional information:
Above is one example I researched in depth, it happens too on more sensors on more days/weeks/months. All have in common that there is no data in the statistics for that sensor on the start of the period due to various reasons.

[viking2010]

I'm getting the exact same issue and have the same version numbers as in the above post. I'm getting results daily like this:

I have a few plugs that recently have started dropping connections every so often, and as @rrozema indicated, this issue looks to be caused by the drop in connectivity. This is then causing the large spikes in data as you can see in the image. For the entity Tumble Dryer in the above image, today's usage should be zero not 92.6kWh (I'd hate the bill for that one!). However, the same entity in the Energy Dashboard is reflected correctly, it's just in the Sankey chart it's incorrect.

Hope this is something that's fixable as I love this integration.

[rrozema]

To retrieve the correct values for a statistic with has_sum = 1 you can use something like following query. This query should be compatible with most sql dialects. I have posted another variant before that used the sqlite-specific lag() or lead() window function, but this may not be easily ported to other sql dialects. This new variant below should run on most sql engines without modification or it should be easy to port if it doesn't run at first.

select ss.start_ts, se.start_ts as end_ts, se.[sum] - ss.[sum] as [difference] from statistics ss inner join statistics se on se.metadata_id = ss.metadata_id and se.start_ts > ss.start_ts and not exists (select * from statistics se2 where se2.metadata_id = ss.metadata_id and se2.start_ts > ss.start_ts and se2.start_ts < se.start_ts) where ss.metadata_id = 82 and ss.start_ts >= 1688760000.0 and ss.start_ts < 1688810400.0 order by ss.start_ts ;

This query will give you the difference values without having to explicitly read 2 rows to find the difference in the sum value, thereby avoiding the pitfalls of having to find the last and/or first row's values that could be outside the specified range. (I picked some random values for start_ts, end_ts and meatdata_id, you will of course have to replace those by the ones for which you want to get the statistics).
Note: the above query was written to return the difference between the specified timestamp's sum-value and that of the next available timestamp. I am not completely confident if this what you need for your code, so here is another example that reads the difference between the specified timestamp's sum-value and the previous available timestamp's sum-value:

select ss.start_ts, se.start_ts as end_ts, se.[sum] - ss.[sum] as [difference] from statistics se inner join statistics ss on ss.metadata_id = se.metadata_id and ss.start_ts < se.start_ts and not exists (select * from statistics ss2 where ss2.metadata_id = se.metadata_id and ss2.start_ts < se.start_ts and ss2.start_ts > ss.start_ts) where se.metadata_id = 82 and se.start_ts >= 1688760000.0 and se.start_ts < 1688810400.0 order by se.start_ts;

I leave it to you which should be used to match your chart code's logic.

[soeffi]

Having the same issue over here.

My guess is, that this is caused by

ha-sankey-chart/src/energy.ts

Lines 117 to 120 in cfe90f2

	const startSum = values[0].sum;
	if (startSum === null) {
	return endSum;
	}

which returns the absolute meter value endSum instead of a difference, when the first data point returned by the statistics does not exist.

I can see two ways to address this:

Option 1:
Instead of using values[0].sum with fixed index 0, find first non-null sum value in the array and use this as startSum

Option 2 (probably the safer choice):
The situation of missing data points has caused trouble in other situations as well, which led to implementation of pull request home-assistant/core#92823, which adds the statistics type "change", that performs the calculation of the differences directly in the back-end.
--> If I understand correctly, instead of calculating the difference between last and first "values.sum" manually, you could just sum up the "changes":

return values.reduce((partialSum, v) => partialSum + v.change, 0);

@MindFreeze, does this help to look into the topic? I'd love to see this resolved. Unfortunately though, my capabilities are probably too limited to provide a sane pull request...