Collect long-term statistics

[emontnemery]

Summary:

Compile long term statistics.

We should be able to collect long-term statistics, for example year's worth of temperature measurement, energy usage etc. without relying on external tools.

High level proposal:

1. Add a new table to the recorder's database in addition to the states table which collects a much smaller amount of data and thus can store much longer periods of data (the recorder defaults to 10 days) without unreasonable growth of the database size.
   The new table would for example hold hourly min, max, mean for a temperature sensor and hourly consumption for a utility meter.
2. Allow the recorder to schedule jobs to collect periodical data from integrations, e.g. hourly statistics from sensor and utility_meter integrations.
3. Make it possible for integrations to inject historical data in the database, e.g. inject historical data from a personal weather station.
4. Add APIs to query the recorder for long-term data

A proposed MVP is to implement 1, 2 (for a very limited set of entities) and 4
An initial draft of 1 and 2 is implemented in home-assistant/core#49852

[dgomes]

I think we should use a modern take on Round-Robin-Databases (RRD)

https://community.home-assistant.io/t/storing-your-very-long-history-rrd-recorder/222758

My custom component is based on the C library, but we could re-implement the RRD principles

[balloob]

Can you be more specific how you think RRD principles can be applied? Data storage or generation?

[dgomes]

Data storage

The principle in a RRD database is that as time passes you can aggregate information (saving space). You might want to look into the temperature of your room for the each minute of the past hour, but only hourly averages of yesterday, the daily average of last week, etc etc (this example uses the average function to aggregate, but the function should be configurable)

More information
https://oss.oetiker.ch/rrdtool/tut/rrd-beginners.en.html

[emontnemery]

This is quite interesting.

The gist of this proposal is to do data aggregation to save storage space as well as time when fetching historical data.

I opened a PR with a rough draft implementation, home-assistant/core#49852, which does data aggregation on an hourly basis but no further levels of aggregation.
With hourly aggregation, 10 year's worth of data results in ~88k data points which seems manageable for home use with a reasonable amount of sensors.

I did a test with home-assistant/core#49852:

• Configure HomeAssistant to use local SQlite database (default) + wipe the database
• Add 100 temperature sensors
• Generate 1 year of hourly statistics for the temperature sensors
  • One row for each entity and hour, the rows have the following columns:

    CREATE TABLE statistics (
    id INTEGER NOT NULL, 
    entity_id VARCHAR(255), 
    period VARCHAR(6), 
    start DATETIME, 
    "end" DATETIME, 
    mean FLOAT, 
    median FLOAT, 
    min FLOAT, 
    max FLOAT, 
    sum FLOAT, 
    PRIMARY KEY (id)
    )

• The database grows to ~160MB, so around 1.6MB / year and sensor in this case

[balloob]

I think that this is a good idea. Decoupling the statistics from entity states will release us from constraints by the state machine. For example, this will also solve the often requested feature to import historical data into Home Assistant.

Since this data is only used for rendering graphs, it's fine that it is no longer coupled to the state machine.

I think that it's still good to keep track if a statistic belongs to an entity ID so we can easily show a graph in the more info dialog. Since not all imported statistics can be tied to an entity, should we allow linking it to a config entry ID?

For time series, would you just want to save a dump every hour? How will that work if an integration can only import data that wraps 24 hours ?

[rostislav-palivoda]

Thanks to @dgomes for pointing me to this discussion.

Keeping aggregated data separate sounds like a good idea to reduce lock time of states table. Still I'm under impression that if values will be in another table then it might be complex to feed the data in to the UI templates and charts. I'm not so familiar with HA core, maybe somebody already done such kind of analysis?

If you don't mind, I'll leave here a link to the RFC I've created today... maybe it will be useful for someone ...or maybe you can comment it - https://community.home-assistant.io/t/anyone-interested-in-a-reports-for-utility-meter-or-maybe-historical-sensor-value/304841?u=palivoda

[emontnemery]

@rostislav-palivoda Thanks for linking to the community thread. Aggregating data from utility type meters (energy, gas, water) is one of the main drivers for adding statistics functionality.

[Danielhiversen]

Future values
Might be out of scope for this discussion, but it is related to handling weather and energy data.

Both weather data and energy prices have future data values.
Today we store future weather data as attributes, which makes the same values stored multiple times in the database.

Make it possible for integrations to inject historical data in the database, e.g. inject historical data from a personal weather station.

Maybe we could add support for future values also?

The data should be available both for visualization and automation.

[emontnemery]

@Danielhiversen In the case of historical data we would expect either integrations or HA itself to compile the data and then never update it again. For future data it will be different, we would need to update that until the future is in the past so to speak?

[Danielhiversen]

Yes, at least for weather data. Energy prices are known 12-36 hours ahead.

[rostislav-palivoda]

Good day gentlemans, let me join this interesting discussion :) As a next step after aggregate component proposal (see link to the RFC above) I thought about possibility to apply trends on top of aggregated values. To follow existing nice HA architecture I suspect it could be done as a separate HA core component.

I'm not sure if trends match the use case with future prices. In the use case above probably user want to apply specific price after specific date. Correct me if I'm wrong. Still let me share how I see it.

Instead of mixing actual and prediction model data together we could keep it in a separate entity_id values stream in states table, just as it would be another sensor with values that could keep values in future and in the past also. The values are recalculated by a separate component (i.e. trends) on specified interval. Looks like this concept fits in to existing components architecture, automations, sensor states concept and allow end user use both actual and calculated model values on the same chart in UI.

In case with electricity price change I suspect it could be another component or even integration that feeds in prices as a sensor values (i..e. daily) and then calculate total import/export costs in timer 23:59 automation. By applying aggregate component on top of costs user can create monthly reports, send invoices via automation etc..... but probably its another story about some other project Enterprise Assistant. :)

[sanyatuning]

Hi guys!
What do you think, can the recorder support TSDB?
Some sort of generic TSDB connector will be awesome.
Just a few: OpenTSDB, InfluxDB, Graphite, Prometeus

[sanyatuning]

Those integrations just export data to TSDB.
I mean recorder may use TSDB instead of SQL DB.
HA needs a new concept to store all statistical data in TSDB, I think.

[emontnemery]

What's implemented now is to compile hourly statistics and store that permanently in a SQL DB.
The amount of storage needed is ~2MB / entity and year, it seems manageable and it's a very big improvement over the states table in the history database: #559 (reply in thread)

I'm not sure it's worth adding a TSDB engine as a native requirement of Home Assistant; I mean what are the expected gains? Significantly more efficient storage? Significantly faster queries?

[scop]

I suppose with TimescaleDB one could conceptually get some of both out of the box, one can just treat it as PostgreSQL on the surface so no specific time series database code needed. I believe TimescaleDB specific functionality would not be used by SQLAlchemy though, so basic HA query usage might not benefit much/any.

But if the table schemas play ball, long term storage, compression, advanced querying with other tools such as Grafana, continuous aggregates etc goodies should just work for other consumers besides HA proper with no code added. Some configuration required from users though.

For this kind of setups, it could be beneficial to make the statistics rollup interval configurable so people could store more precise data if they want and their db can manage it, but it appears to be hardwired to one hour at the moment.

Also, for completeness, haven't tried it out myself, just eyeballed it, but some might find the LTSS custom component interesting: https://github.com/freol35241/ltss

[thecode]

I think supporting various DB engines is a good concept and if there is an engine which is more efficient for storing long term statistics we should try to add it, however I don't like the idea of somehow splitting the data into two DB engines (one for general HA data and one for statistics) this may introduce many problems, confuse users which data is stored were and may reduce the stability of the solution (what happens for example if main DB is inconstant with the history DB).
As I see it although it is somehow related to this discussion it is drifting away from the main purpose of this discussion.
A different discussion about adding support for additional DB engines should be created (assuming you plan to implement it) or a feature request in the forum.

[rostislav-palivoda]

TimeScaleDB initially was introduced as an extension to PostgresSQL. From the documentation I see Postgres is supported by the Recorder (https://www.home-assistant.io/integrations/recorder/) because its still plain SQLs. IMHO the TimesaleDB will save you from situations when you reached limits in writing values per seconds per DB.

Here is blog post about this problem - https://blog.zabbix.com/zabbix-time-series-data-and-timescaledb/6642/
To use beauty of TimescaleDB the table had to be created the special way.

Still I'm not sure you want to keep all this terabytes of 10 years sensor readings in the DB. Most of systems keep only recent data detailed (1 value per second) and then filter our old data (to 1 value per minute) and/or archive it.

[parautenbach]

Issue is closed, but unanswered. Isn't the answer that (today) you can add a state_class to any entity and you'll have it recorded in HA's long-term stats? (I do that for several sensors.)

[frenck]

Yes. However, it was not closed with an answer, but close because of age/staleness.

Please check our developer docs for more information on the long term stats.

../French