Hi,
To get alerted about disconnected sensor, I created a statistical sensor to count how frequent temperature sensor (sensor.temperature_zone_1) is updated for past 6 hours as indicator of good support
sensor:
- platform: statistics
name: "temp_update_count"
entity_id: sensor.temperature_zone_1
state_characteristic: count
max_age:
hours: 6
The sensor.temp_update_count state value is constant at 144 or 158 ±1. Sensor max_age is 6 hours but when I check history db using SQLiteWeb add-on, the number of reported values (row number) of points are much different for same period - only 12 rows given using SQL querry:
SELECT states.state_id,state, DATETIME(last_updated_ts, 'unixepoch', 'localtime'),DATETIME(last_changed_ts,'unixepoch', 'localtime'),DATETIME(last_reported_ts,'unixepoch', 'localtime') FROM states LEFT JOIN states_meta ON (states.metadata_id=states_meta.metadata_id)
WHERE states_meta.entity_id == "sensor.temperature_zone_1"
AND DATETIME(last_updated_ts, 'unixepoch', 'localtime') > datetime('now', '-6 Hour','localtime')
ORDER BY last_updated_ts DESC
If I extract from short term stastitiscal table (5 min freq) I got 72 (6h * 60min/5) which correct in term of report
SELECT statistic_id,mean,min,max, DATETIME(created_ts, 'unixepoch', 'localtime'),DATETIME(last_reset_ts,'unixepoch', 'localtime'),DATETIME(start_ts,'unixepoch', 'localtime') FROM statistics_short_term LEFT JOIN statistics_meta ON statistics_short_term.metadata_id=statistics_meta.id
WHERE statistics_meta.statistic_id == "sensortemperature_zone_1"
AND DATETIME(created_ts, 'unixepoch', 'localtime') > datetime('now', '-6 Hour','localtime')
ORDER BY created_ts DESC
I can’f figure out:
- Is it correct that statistical count sensor value (144) is double of counted rows from statistics_short_term (72) ?
- If yes, how they duplicate count ? based on ts columns: reated_ts/changed_ts/reported_ts ??
- how to count exactly updated times like querry from state table (12) using HA core intergration ?