Hi,
I bought solar pannel since 2 years ago.
I survey production from HA-core on debian sid.
To do some simple operation as get maximum values, i download home-assistant_v2.db.
I see home-assistant_v2.db from sqlitebrowser on debian but i don’t know where is data !
Someone know how is organized sqlite3 database created from HA-core ?
I have as id entity as exemple : sensor.opendtu_fbfe0c_yield_day
Integration is : MQTT
Thanks a lot
What are you actually trying to do and how are you consuming. There are methods in HA to extract data from the dB. Specifically recorder.get_statistics was just released in 2025.6
If you actually need direct then the schema is documented in the developer docs.
I need to get all data from yield_day to get the maximum value for solar panel production for each year.
Output to? As what format?
From the database sqlite3 to In .txt or .csv !
I have the id for the entity, but where is located data ?
If you just want to dump the data to .csv, this is what you’re looking for:
For where the data is in Home Assistant’s home-assistant_v2.db, read the schema:
Look into states and statistics tables.
Nathan’s got the answer. But I’ll offer another idea for possible future use.
You can save data directly to a csv file and skip the database. Use the File integration to set define the output file. Then create an automation to use action: notify.send_message to send data to that file. You can set the automation to trigger each time an entity changes, or once a day to record summary data like total, min and/or max values, or whenever you need.
Why i only have 10 days from my database ?
I get database and dump data named “state” from specific “attributes_id” from table states.
sqlite3 -header -csv /home/homeassistant/.homeassistant/home-assistant_v2.db "SELECT state_id, state, last_updated_ts, attributes_id FROM states WHERE last_updated_ts>1592334844 AND attributes_id=6531;" > export_infinity_attributes_6531.csv
And, O surprise my friend, only ten days ago data 
Where got other data so?
I believe the default Recorder purge_keep_days is 10. After that, old events and states data are purged and, for some types of data, summarized in the statistics tables.
This one-size-fits-all approach is less than ideal, but there are so many possible ways to use HA, and so many different requirements end users have, that we each need to develop a retention strategy for which works for us. That takes some effort.
Some ways to address this include saving the data elsewhere, as I mentioned above, or setting purge_keep_days to a large number and then creating automations to selectively purge those data which only need shorter retention periods.
Be careful though. Some entities excessively spam the database. With a long retention period, you risk a bloated database which slows down backups and updates, and is more easily corrupted.
As for how to get it, presumably you could query the statistics tables. I haven’t done so, so I can’t be much help there.
Live data is states.
History > live stats and the live stats are distilled down in the history as they fall out
The UI blends both
