Hi,
I’m trying to get the state/temperature (of yesterday) from a sensor to display in HA, but I don’t know if there’s a easy way to do this.
I’ve looked through tons of Google Results and found nothing so far.
If anyone knows how to accomplish this, I would be very glad.
Thank you.
average temperature of the whole day or at a specific time?
That doesn’t make sense, if you’re getting a temperature from the day before, it wouldn’t be the current temperature.
So what do you want?
The average temperature of the previous day, or the temperature at a specific time?
Oh, I’m sorry!
Wrong thread! I thought I was responding to another thing.
No, I just want the temperature at a specific time (yesterday at 22:15:xx, for example)
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)?
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)
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.
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.
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')}}