Statistic card - when period is rolling window it only access the last 11-12 days of data

[Landiss]

The problem

I initially created this issue in frontend, but was told this is likely a backend issue, so I'm copying it here.

When setting up a statistics card to show data based on long statistics sensor, and using rolling window as period, it only seems to include data from the last 11-12 days. That is, setting the rolling window to anything over 12 days does not change the displayed result, even though there is older data for the sensor.

Here's my specific configuration:

type: statistic
stat_type: change
entity: sensor.fv_today_production
period:
  rolling_window:
    duration:
      days: 365

At the moment, this produces a result of 272.84 kWh, same as if I change days to 12. However, from the energy dashboard that uses the same sensor, I can see the actual value from the last 365 days should be around 7500 kWh.

What version of Home Assistant Core has the issue?

core-2024.4.1

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant OS

Integration causing the issue

No response

Link to integration documentation on our website

https://www.home-assistant.io/integrations/recorder/

Diagnostics information

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

A comment from the issue in frontend suggesting this is backend problem:

Might be a core issue. Frontend doesn't compute this number, it sends the request to backend. See I get same response for sending days:14 and days:15.

Originally posted by @karwosts in home-assistant/frontend#20469 (comment)

[home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (recorder) you are listed as a code owner for? Thanks!

Code owner commands

Code owners of recorder can trigger bot actions by commenting:

• @home-assistant close Closes the issue.
• @home-assistant rename Awesome new title Renames the issue.
• @home-assistant reopen Reopen the issue.
• @home-assistant unassign recorder Removes the current integration label and assignees on the issue, add the integration domain after the command.
• @home-assistant add-label needs-more-information Add a label (needs-more-information, problem in dependency, problem in custom component) to the issue.
• @home-assistant remove-label needs-more-information Remove a label (needs-more-information, problem in dependency, problem in custom component) on the issue.

_{^{(message by CodeOwnersMention)}}

---

recorder documentation
recorder source
_{^{(message by IssueLinks)}}

[karwosts]

Looking at this a bit I think the bug might be here:

core/homeassistant/components/recorder/statistics.py

Lines 1302 to 1311 in d4500cf

	if (
	head_start_time is not None
	and (
	oldest_sum := _get_oldest_sum_statistic_in_sub_period(
	session, head_start_time, StatisticsShortTerm, metadata_id
	)
	)
	is not None
	):
	return oldest_sum

When we look for a change over a long period, we crack the time window into the head (fraction of hour at the start), the main (complete hours over the period), and the tail (fraction of hour at the end).

We look for the oldest statistic to compute the start of the diff for the change, but for the head period we look in the short term statistics table for the oldest entry.

The function get_oldest_sum_statistic_in_sub_period doesn't reference the head end time, so it gets the oldest short term statistic (which would only be ~10 days old), ignoring that the main window starts much before that.

Out of time at the moment to look further but that's what it seems like so far.