Is possible to create a table that displays all historical values of an entity?
For example:
- cost of devices each day, moths etc
- calories burned each week, months etc
Is possible to create a table that displays all historical values of an entity?
For example:
Am looking for something that sounds similar… My current use case is that i want to have a table of my daily EV-charging total. My charger, a Zappi, has a sensor counting the charge.
I found Editing historical sensor data in Home Assistant with SQLite web — Adriaan Rol’s Homepage , and was able to retrieve the value for my sensor with the following query:
SELECT * FROM states_meta AS sm, states AS s
WHERE s.metadata_id=sm. metadata_id AND sm.entity_id = “sensor.myenergi_zappiteeuwen_charge_added_session”
AND s.state <> “unavailable”
ORDER BY s.last_reported_ts DESC
In my case with my sensor, i get multiple value per day, while i only want the daily ‘end’ value, so i’ve created a new energy helper that daily resets.
With the following SQL (thank you ChatGPT) i can make a list of the max value for each data of that helper sensor:
WITH date_range AS (
SELECT
DATE(‘now’, ‘start of year’, ‘-1 year’) AS start_date,
DATE(‘now’) AS end_date
)
SELECT
DATE(datetime(s.last_reported_ts, ‘unixepoch’)) AS report_date,
MAX(CAST(s.state AS FLOAT)) AS max_state
FROM
states_meta AS sm
JOIN
states AS s
ON
s.metadata_id = sm.metadata_id
WHERE
sm.entity_id = “sensor.daily_zappi_charged”
AND s.state <> “unavailable”
AND DATE(datetime(s.last_reported_ts, ‘unixepoch’)) BETWEEN (SELECT start_date FROM date_range) AND (SELECT end_date FROM date_range)
GROUP BY
report_date
ORDER BY
report_date DESC;
I used the bottom SQL also on the Zappi sensor, but only get 11 days of data. So i need to find out how to store more days of data of that sensor.