use the influxdb add-on in home assistant to store sensor data to be used for ML predictions

[scruysberghs]

Now home assistant recorder stores data by default in the sqlite db and default purging is set to 10 days. You can use other databases but if you want to keep more data for emhass for the ML predictor for the sensor_power_load_no_var_loads for example you have to increase the setting for all sensor data what will lead to huge databases.
In my opinion (and sorry if this has been discussed before, but i couldn't find it) a time series database would be a better match for this.
If you use the influxdb add-on for home assistant for example all (or a filtered list of) your sensor data is stored there in an efficient way, easy accessible by emhass, with the retention settings set in the influxdb add-on.

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

From emhass/python you can do:

from influxdb import InfluxDBClient

# InfluxDB connection settings
INFLUXDB_HOST = "homeassistant"
INFLUXDB_PORT = 8086
INFLUXDB_USER = "homeassistant"
INFLUXDB_PASSWORD = "my_pass_for_the_homeassistant_influxdb_user"
INFLUXDB_DATABASE = "homeassistant"

# Define time range
DASHBOARD_TIME = "now() - 30d"  # Example: Last 30days 
UPPER_DASHBOARD_TIME = "now()"

# Connect to InfluxDB
client = InfluxDBClient(host=INFLUXDB_HOST, port=INFLUXDB_PORT, username=INFLUXDB_USER, password=INFLUXDB_PASSWORD, database=INFLUXDB_DATABASE)

# Define query
query = f'''
    SELECT mean("value") AS "mean_value" 
    FROM "autogen"."W" 
    WHERE time > {DASHBOARD_TIME} AND time < {UPPER_DASHBOARD_TIME} 
    AND "entity_id"='sensor_power_photovoltaics' 
    GROUP BY time(10s) FILL(null)
'''

# Execute query
result = client.query(query)

# Print output
for point in result.get_points():
    print(point)

# Close connection
client.close()

To get your data like this

{'time': '2025-03-15T15:59:30Z', 'mean_value': 503.1366666666666}
{'time': '2025-03-15T15:59:40Z', 'mean_value': 501.23333333333335}
{'time': '2025-03-15T15:59:50Z', 'mean_value': 503.64250000000004}
{'time': '2025-03-15T16:00:00Z', 'mean_value': 499.54749999999996}
{'time': '2025-03-15T16:00:10Z', 'mean_value': 501.545}
{'time': '2025-03-15T16:00:20Z', 'mean_value': 488.7475}
{'time': '2025-03-15T16:00:30Z', 'mean_value': 493.57000000000005}
{'time': '2025-03-15T16:00:40Z', 'mean_value': 490.63999999999993}
{'time': '2025-03-15T16:00:50Z', 'mean_value': 488.91}
{'time': '2025-03-15T16:01:00Z', 'mean_value': 479.6}
{'time': '2025-03-15T16:01:10Z', 'mean_value': 482.4}
{'time': '2025-03-15T16:01:20Z', 'mean_value': 466.7733333333333}
{'time': '2025-03-15T16:01:30Z', 'mean_value': 450.405}
{'time': '2025-03-15T16:01:40Z', 'mean_value': 446.5025}
{'time': '2025-03-15T16:01:50Z', 'mean_value': 453.97}
{'time': '2025-03-15T16:02:00Z', 'mean_value': 447.7025}
{'time': '2025-03-15T16:02:10Z', 'mean_value': 446.2925}
{'time': '2025-03-15T16:02:20Z', 'mean_value': 437.35999999999996}

Added benefit is that you can use the Grafana add-on for energy dashboarding....

Toughts?

[davidusb-geek]

It could be a nice alternative. I do use influxdb and grafana myself for visualization and backup storage of my data in an efficient db designed for time series as you said.
However I have not see the need to implement this myself as the default sqlite db works just fine to retrieve data. And if you set your recorder correctly with the list of sensors that you want to keep in the db then it works fine. I have a lot of sensors defined in my recorder and a purge set to 365 days, my db is stable now at 5GB which is fine to me.
But like I said before I do see the interest of the influxdb option, so open to any contributions to implement this