Bump. Anyone?
It’s not possible. Best you can get is a average temperature of a time period usingstatistics sensor.
If this is something you know you’re going to need ahead of time, would it be possible to have an automation store the current temperature away so that you have it available for tomorrow (either in a variable, or a database, or even simply just a file)?
1 Like
I know it’s possible to access the database directly, but I’m not sure for what to query/search.
It probably begins like this:
"SELECT * FROM states WHERE entity_id = 'sensor.pool_temp' AND ... time?
That’s the problem, its not a simple query. You’d need to intelligently search though the states and choose one that represents the time frame you are looking for.
Example: You want to get the state at 4pm. The last state changes were at noon and 9pm. You intelligently need to choose the noon state.
That’s why I was suggesting preemptively storing the desired data. The data would obviously be missing if the system was down the previous day, but might be workable.
All of this, of course, assumes there isn’t a website that could be used to extract the data from… similar to what was worked on for the Hours of Daylight discussion.
I wasn’t discounting your solution, I was just telling @Valentino_Stillhardt that what he wants (looking through history) is easier said than done.
EDIT: I also think a custom component could probably get the job done. Or possibly a new component.
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)
6 Likes
Is it possible to get these values in easier way? I mean f.e. developer tools? 
In your way I need to realod HA every time I change date or hour 
It’s a sensor, its always 24 hours behind. It’s updated based on the scan interval which is 30 seconds. Make an automation that performs your math and trigger it at the time you want to get the value.
2 Likes
I was searching for something similar. Here’s my implementation without querying SQL directly in case anyone is interested:
- platform: template
sensors:
yesterdays_kwh:
friendly_name: "Yesterday's kWh"
entity_id: []
value_template: >-
{% if now().hour == 23 and now().minute == 59 -%}
{{ states('sensor.total_kwh') }}
{%- endif %}
unit_of_measurement: "kWh"
It updates a template sensor value only at 11:59pm from the source sensor and stores that value for 24 hours until the next day when it refreshes again.
1 Like
Good solution, but people should realize that the difference between sensors is that yours is always at 23:59, where the SQL look up is exactly 24 hours in the past from the current time.
How about an automation that runs at 22:15 and updates the value of an input_number with the current temperature.? That should also survive a restart.
Problem with solutions like this is that it will “reset” during your current day. Where as @Valentino_Stillhardt‘s solution will stay for the full duration of the day. I.e. if you set the time to 22:00 and at 23:00 you look at the sensor, the info is from an hour ago instead of the previous day. So the automation route and @jwoznys solution only really work at midnight.
Just make an automation that triggers at the time you want to keep the data from on that day for the next day, and put the data in a variable. I do something similar here, though I’m triggering on a state change rather than at a certain time, but same concept:
- id: posta_state_change
alias: 'Posta state change'
initial_state: 'true'
trigger:
platform: state
entity_id: sensor.cp_packages_coming_today
condition:
condition: state
entity_id: 'sensor.cp_packages_coming_today'
state: 'Delivery'
action:
service: variable.set_variable
data:
variable: posta_variable
attributes_template: >
{
"from": "{{ state_attr('sensor.cp_packages_coming_today', 'from') }}",
"date": "{{ state_attr('sensor.cp_packages_coming_today', 'date') }}",
"subject": "{{ state_attr('sensor.cp_packages_coming_today', 'subject') }}"
}
data_template:
value: >
{{ states('sensor.cp_packages_coming_today')}}
1 Like
Ah yes, true. Could solve that with two input_numbers: the first one as above, and the second one populated by another automation that copies the value of the first into the second at midnight. That would then be “temp at 22:15 yesterday” whenever you look at it.
1 Like
Yes you can just keep chaining them for how many history items/what period you need.
Why not this instead?
query: 'SELECT * FROM states WHERE entity_id = "sensor.temp" AND state != "unknown" AND last_changed < DATE_SUB(now(), INTERVAL 1 DAY) ORDER BY state_id DESC LIMIT 1;'
If there’s concern about a large database, one could add another AND statement so that values older than (for example) 1.1 days don’t get included.
Thx for your solution - seems to solve my problem (I would like to store the yesterday’s total percipitation) - but using your sensor implementation (at 23:55) - does not store the value at all. Trying to set it (via the DEV-tools) shortly stores it - but it looks like the sensor is reset after a short time. Any idea, why this could happen?