To be able to compile better SQL queries, I took a closer look at the Home-Assistant database schema. Initially mainly to correct certain historical data, later to improve the energy forecast of my PV system, which enables more efficient control of the heating. Details on the queries for the PV forecast will follow in a separate article..

Direct queries or changes to the database can be made using SQL queries:

See also the different options for direct access to the database: 3 variants: SQLite database access - Home Assistant

A look at SQLite3 Editor in VSCode shows the individual tables and their relationships.

The "states" table is interesting for current data within 10 days. The "statistics" table also provides older data: One value per hour.

States data (current values of all entities)

The unique identifiers (entity_id) of all entities are listed in the "states_meta" table:

The actual values of the entities are stored in the"states" table, using the metadata_id:

The listed values in this example therefore all belong to "sensor.pv_voltage_dc", as the metadata_id "20" is used and this is translated to "sensor.pv_voltage_dc" in states_meta. The attributes_id refers to an additional entry in the "state_attributes" table. The data of the 3 tables can be summarized and output in a readable format using a customized SQL query:

Last hour, all status - data of all entities including entity_id and shared_attrs:

The following query lists all status information of all entities for the last hour:

SELECT STRFTIME('%Y.%m.%d %H:%M', datetime(last_updated_ts, 'unixepoch', 'localtime')) AS date,
    states_meta.entity_id,
    states.state,
    shared_attrs
FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE last_updated_ts BETWEEN strftime('%s', 'now', '-1 hour') AND strftime('%s', 'now')

Current status for a specific entity

SQL query:

select datetime(last_updated_ts, 'unixepoch', 'localtime') AS readabledate,state from states where metadata_id = ( SELECT metadata_id FROM states_meta
WHERE entity_id = 'sensor.flowmeter_sum')  order by last_updated_ts desc limit 10;

Output:

Current status for a specific entity including shared_attrs

In addition to the current status (state), certain entities have additional properties, stored in an additional column: shared_attrs. The following query can be used to read an entity and its additional properties (shated_attrs):

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id = 'sensor.fronius_storage_chast' order by last_updated_ts DESC limit 1

Output:

Status of a specific entity at a specific time, for example yesterday at the same time

SQL query

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as date,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id = 'sensor.fronius_storage_chast' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-1 days')) order by last_updated_ts DESC limit 1

Output:

Entities with additional data in shared_attrs: e.g. weather data

The following query retrieves the last ten data records for the "weather.home" entity. Included are the formatted date, the entity ID, the state and common attributes from the shared_attrs table. The results were sorted by the last update time in the following SQL query:

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id like '%weather.home' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-0 days')) order by last_updated_ts DESC limit 10

Output:

To display certain values from the shared_attrs column as separate columns, the JSON data can be extracted with the following SQL query:

SELECT STRFTIME('%Y.%m.%d %H:%M',datetime(last_updated_ts, 'unixepoch', 'localtime')) as readabledate,states_meta.entity_id,states.state,json_extract(shared_attrs, '$.temperature') AS temperature,json_extract(shared_attrs, '$.cloud_coverage') AS cloud_coverage, shared_attrs FROM states
INNER JOIN states_meta ON states_meta.metadata_id = states.metadata_id
INNER JOIN state_attributes ON state_attributes.attributes_id = states.attributes_id
WHERE states_meta.entity_id like '%weather.home' and 
last_updated_ts < (SELECT unixepoch(datetime('now'),'-0 days')) order by last_updated_ts DESC limit 10

Output:

Historical data: Values from the statistics tables

Like the status table, the statistics tables do not directly use the unique entity ID (e.g. sensor.temperature), but a meta table (statistics_meta) for the translation of the entity ID (column: statistic_id) to an id (number)

As an alternative to the status table (states), the values of a specific entity can be read from the "statistics" table using an SQL query. The metatdata_id is also translated here via a sub-query from the statistics_meta table:

Database Query:

select datetime(created_ts, 'unixepoch', 'localtime') AS readabledate, * from statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.flowmeter_sum') AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) > strftime('%Y%m%d', datetime('now','localtime','-3 days'));

Output:

The statistics table has another special feature: Depending on the sensor type, different columns of the database table are used: Sensors of the type state_class=total or total_increasing store their values in the columns: "state" and "sum". A sensor of the type "state_class = measurement" uses the columns mean, min and max.

Here is an SQL query for a sensor of the type: "state_class = measurement":

select datetime(created_ts, 'unixepoch', 'localtime') AS readabledate,* from statistics WHERE metadata_id = ( SELECT id FROM statistics_meta WHERE statistic_id = 'sensor.eg_temperatur') AND strftime('%Y%m%d',datetime(created_ts, 'unixepoch','localtime')) > strftime('%Y%m%d', datetime('now','localtime','-3 days'));

Output:

The "statistics" table saves hourly values without deleting them. The statistics_short_term table is different: it saves the values every 5 minutes and deletes the data after 10 days, just like the "states" table. The database layout of the statistics and statistics_short_term tables is identical. Both use the statistics_meta table and both fill state and sum or mean, min and max depending on the sensor type.

As an example, for the detailed view of a sensor for: Minimum, mean and maximum, the data from the "mean", "min" and "max" columns of the "statistics_short_term" table are used:

Conclusion

The Home Assistant database schema allows efficient evaluation of entity data through the use of tables for current and historical information. The "states" table provides access to current data, while "statistics" is used for long-term data with an hourly interval. Entities and their additional attributes can be evaluated individually and in a targeted manner using SQL queries.