Long-term statistics: websocket "history/statistics_during_period" API

[koying]

The problem

I'm playing with the (not yet documented) long-term history/statistics_during_period websocket API and there is something in the result quite confusing.

As an example, here is a response

{
    "id": 1,
    "type": "result",
    "success": true,
    "result": {
        "sensor.exterieur_temperature": [{
                "statistic_id": "sensor.exterieur_temperature",
                "start": "2021-09-10T08:00:00+00:00",
                "mean": 21.926369901972222,
                "min": 21.3,
                "max": 22.3,
                "last_reset": null,
                "state": null,
                "sum": null
            }
        ]
    }
}

Request (with end_time in the future)

{
    "id": 1,
    "type": "history/statistics_during_period",
    "start_time": "2021-09-10T07:00:00.001Z",
    "end_time": "2021-09-11T09:02:37.932Z",
    "statistic_ids": ["sensor.exterieur_temperature"]
}

By comparison with influxdb, I can see that the aggregations are for temperatures between 8:00 UTC and 9:00 UTC.

However, in the statistics graph, the point is displayed at 8:00 UTC (which is the only available date), so it actually displays an average of the future as far as the graph is concerned, which doesn't make a lot of sense.

Furthermore, just with the API we don't know on what range is the temperature averaged.

Wouldn't it make sens to add an end to the API, then for the frontend graph to use that date instead?

What is version of Home Assistant Core has the issue?

2021.9.5

What was the last working version of Home Assistant Core?

No response

What type of installation are you running?

Home Assistant Container

Integration causing the issue

No response

Link to integration documentation on our website

No response

Example YAML snippet

No response

Anything in the logs that might be useful for us?

No response

Additional information

No response

[probot-home-assistant]

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

[probot-home-assistant]

Hey there @home-assistant/core, mind taking a look at this issue as it has been labeled with an integration (history) you are listed as a code owner for? Thanks!
_{^{(message by CodeOwnersMention)}}

[emontnemery]

Thanks @koying, this makes sense!

Continuing with your example, can you confirm this is what you expect:

{
     "id": 1,
     "type": "result",
     "success": true,
     "result": {
         "sensor.exterieur_temperature": [{
                 "statistic_id": "sensor.exterieur_temperature",
                 "start": "2021-09-10T08:00:00+00:00",
+                "end": "2021-09-10T09:00:00+00:00",
                 "mean": 21.926369901972222,
                 "min": 21.3,
                 "max": 22.3,
                 "last_reset": null,
                 "state": null,
                 "sum": null
             }
         ]
     }
 }

[koying]

@emontnemery Yes, exactly

[emontnemery]

Draft PR here: #56063, tests need to be updated as well
We should consider including a duration instead of or as well as the end time of each data point.

[koying]

Duration is indeed an alternative I thought off.

It could definitely make sense to add it but not replace the end date, imho.
Specifically for graphs, which will be the main consumer in the foreseeable future, that would mean calculating the actual end date over and over again, which seems like a waste of resource.

[balloob]

We should return "duration" as part of a new "metadata" that is part of the top-level result since it should be the same for all results. It makes no sense to return 2000 objects with dates to allow us calculate the duration.

[balloob]

We should also drop statistic_id from the individual measurements.

[koying]

We should return "duration" as part of a new "metadata" that is part of the top-level result since it should be the same for all results

This is only true if it has been decided once and for the end of times that the aggregations rhythm will never change OR that the statistics tables will be emptied when a change occurs.
Otherwise, you might have statistics with different durations, and the API might return a set with mixed ones.

@emontnemery For the same reasons, I don't think "creating" the end datetime at the API level is the proper solution if the conditions above are not met.
If you have a mix of duration in the database, you actually have no clue what the end time is...

Bottom-line: IMHO, and "end" column should be added in the statistics table, and the API should plainly pick that one.

[balloob]

We have a PR open for 5 minute statistics but we're adding it to a different table. This table will always only be for 1 hour results. We should make sure that all statistics from a single API call should always span the same duration.