I’m struggling to understand how to reference a sensor value X days ago when X days is longer than the recorder length (default 10 days) using SQL.
I’ve been successful doing this when X is less than the recorder length (10 days), but can’t figure out how to pull data 28 days ago (for example). Obviously I’m trying to avoid extending the recorder length as the historic hourly mean data is all I need to reference and I don’t want to extend the database size as I already have 3500+ entities.
The SQL integration documentation only references how to do this for data within the recorder window.
I understand that I must use the long-term statistics pulling mean data.
I also found an example at the end of this question that should answer exactly what I’m looking for, but the result of the SQL entity is “unknown”.
My SQL Entity configuration:
My “Select Query”
SELECT
statistics.mean
FROM
statistics
INNER JOIN statistics_meta ON
statistics.metadata_id = statistics_meta.id
WHERE
statistics_meta.statistic_id = 'sensor.strava_stats_summary_all_run_distance'
AND start_ts <= strftime('%s', 'now', '-28 days')
ORDER BY
start_ts DESC
LIMIT
1;
Any help would be much appreciated!
