After a lot of trial and error I taped this SQL query together:
# Get the sensor value at a specified time
- platform: sql
queries:
- name: Temp. piscina ontem
query: "SELECT * FROM states WHERE entity_id = 'sensor.temperatura_piscina' ORDER BY ABS(strftime('%s', created) - strftime('%s', '2019-11-07 12:00:00', 'utc')) LIMIT 1;"
column: 'state'
Although the time (hour) seems shifted in the state window, it seems that it’s getting the right value. Timezones can be a …argh.
It works. However, at least two people on StackOverflow said that this solution is NOT efficient and will slow down massively on very big databases (multiple GBs). Random Google reader, keep that in mind - please.
The SQL query, in my understanding, works by subtracting both times and then taking the absolute (removing the minus sign, basically). Then it sorts the results by smallest difference and displays the first one (which is the smallest difference).
Used references:
https://www.home-assistant.io/docs/backend/database/ (Database field names)
https://www.sqlite.org/lang_datefunc.html (strftime)
https://stackoverflow.com/a/27401449/3525780 (Didn’t work, but gave me the starting point)
# Get the sensor value yesterday at the same time as today - Only partially tested!
- platform: sql
queries:
- name: Temp. piscina ontem
query: "SELECT * FROM states WHERE entity_id = 'sensor.temperatura_piscina' ORDER BY ABS(strftime('%s', created) - strftime('%s', datetime('now'), '-1 day')) LIMIT 1;"
column: 'state'
#scan_interval: 30 (default)