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?
Because that template sensor clears the value out every time states(‘sensor.total_kwh’) changes. If the time is 11:55pm, then it stores the value. Any other time it changes, it clears the value. You’d need to add an else for it to work properly. I’m not sure how his was working in the first place.
- 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') }}
{%- else %}
{{ states('sensor.yesterdays_kwh') }}
{%- endif %}
unit_of_measurement: "kWh"
Super, seems to work now - thank you, petro. One other question is: what happens, if the “triggering” sensor does not change exactly within that hour and minute?
then nothing gets stored. You’d have to add sensor.time into the template.
I know i’m reacting to a quite old post, but it is almost the thing i’m looking for.
Is it possible to get the value for the last day of a specific month, but without hardcoding the specific date?
Hey everyone, kind of late to the party but, here is the query I used, in case it helps anyone:
Hi is it possible the syntax should be
- platform: template
sensors:
verwachte_temperatuur:
friendly_name: "Temperatuur vandaag"
value_template: >-
{% if now().hour == 23 and now().minute == 59 -%}
{{ states('sensor.buienradar_temperature_1d') }}
{%- endif %}
unit_of_measurement: "°C"
I get an error when using the part:
entity_id: []
I also want to have/port the yesterday predicted expected temperature to be available today. Because when I look at the today temperature, it displays the current temperature (which in the morning can be totally off what it is gonna be today). And in the morning i’d like to know what the expected temperature is gonna be today so I can dress to it properly (simple question isnt’ it haha). And the only place to find that is in the history of what was predicted yesterday.
his template never worked, see my comment below solving the issue. entity_id is also deprecated, the good news is that you don’t need it and the template will still work.
EDIT: Also, that template requires you to wait until after midnight to see the actual sensor change occur. If you use @krash’s link, it doesn’t have that problem.
1 Like
So @krash link query is not putting a draw on resources like mentioned above?
Just for fun-sake. I was wondering why the template wouldn’t work. Indeed you’re right. If I enter it in the config.yaml under sensors it doesn’t work…
But if I enter it in the developer templating-test-tool. It does work… why??? Is it because the reboot should occur at exactly that that timeframe? But would the template be useful if I put it in an automation or script? I like the simplicity of it…
It won’t work in the template editor either because your times won’t match. If it’s working for you, you’re doing something wrong. That template will only work once it passes midnight once. Then it will stay that value until the next midnight if you use the updated template I posted awhile ago. The template you posted in this post will only work from 23:59 to 00:00. All other times it will be unavailable.
FYI, an updated version of the template using the new template sensor integration would be:
template:
- trigger:
- platform: time
at: "00:00:00"
sensor:
- name: Temperatuur vandaag
unique_id: verwachte_temperatuur
state: "{{ states('sensor.buienradar_temperature_1d') }}"
unit_of_measurement: °C
Just keep in mind that it will be unavailable until the trigger fires. You can add a home assistant startup trigger in there too if you want it to refresh at startup. However, it will not be the midnight time.
1 Like
I have 6 sensors querying this way. I have not noticed any delay.
Nothing big enough to be noticeable at least.
1 Like
Because you don’t place it in sensor.yaml. it goes in the template section.
1 Like
I’ve found this old thread.
Just in case anybody is interested in getting last day’s sensor data every hour, you can check this post: https://community.home-assistant.io/t/maintain-an-array-with-last-values-of-a-sensor/368806/6.
The related part to this post is the automation, where I maintain last 25 values of a sensor (every hour since yesterday at this hour) with an first-in first-out buffer made with an input_text.
In the template sensor I extract values of the buffer and process them to get a simple estimation of next hours linear-weighted average temperature.
I made it using the sql integration. The suggested queries didn’t worked for me, so I wrote my own: Here it is:
SELECT state FROM states WHERE entity_id = 'sensor.YOURSENSOR' AND CAST(strftime('%s', last_updated) AS integer) > (CAST(strftime('%s', 'now') AS integer) - 86400) ORDER BY last_updated LIMIT 1
And I don’t think this one will have troubles with TZ or DST. The compare is done on unix time stamps (%s).
What I still don’t know is how to control the refresh rate of this sensor. If you have any tip, let me know 
3 Likes
When I try to use that I get:
1305 - FUNCTION homeassistant.strftime does not exist
STRFTIME is specific to sqlite. That 1305 comes from MySQL, doesn’t it?