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.
I have 6 sensors querying this way. I have not noticed any delay.
Nothing big enough to be noticeable at least.
Because you don’t place it in sensor.yaml. it goes in the template section.
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 
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?
Yeah I’m using MariaDB. I think I’ve got it:
SELECT state FROM states WHERE entity_id = 'sensor.total_cost_today' AND HOUR(TIMEDIFF(UTC_TIMESTAMP(), last_updated))>=24 ORDER BY last_updated DESC LIMIT 1
I started using Grafana for this (and other reasons).
It’s strange that nobody mentions the existence of it?
EDIT: this is mainly addressed to those looking for a way to have data from the past
I needed a sensor not a graph.
The querying of the DB indeed takes a lot of CPU power; my Pi4 increased from 30% CPU usage to 50%, which really slows the overall experience of HA.
Solution: disable the updates for the SQL sensor and update it via an automation at the frequency you want.
see: SQL - Home Assistant scan interval
In summary: disable the updates in the SQL integration (via the 3 dots and system options).
Next: make an automation to update the sensor, eg. every 10 minutes.
Example:
- alias: update van zonneproductie gisteren op dit uur
id: "6f18655a-e923-46b5-9a83-8d3340458e83"
description: ""
trigger:
- platform: time_pattern
minutes: /10
condition: []
action:
- service: homeassistant.update_entity
data: {}
target:
entity_id: sensor.zonneproductie_gisteren_op_dit_uur
mode: single
Note: I defined the SQL sensor in the configuration.yaml file and saw that the sensor was mentioned 2 times in the SQL integration. I have the impression that you have to define the SQL sensor via the GUI only instead of the configuration.yaml because you cannot disable the updates via the config file.
Anyone could help?
I need to make query to show sensor state from 23:59 form yesterday and 23:59 from the day before yesterday.
I tried the queries given above but looks like my scropt is wrong as the integration doesn’t accept the query.
Thank you!
Please do not cross post on multiple threads. If you have a question, create a single new topic.
The timestamp spec in the database has changed in recent schema, so old SQL queries may no longer work. If you can wait a day to start using your data, I’d suggest using an automation to record the temperature in advance. Another option would be a trigger-based template sensor, but I’m not convinced they would do what you want if the system restarted. Something like this, with two input_number helpers already set up:
alias: Temperature recorder
description: >
Records the temperature at 23:59 into input_number.temp_yday, dropping
the previous value into input_number.temp_day_before.
trigger:
- platform: time
at: "23:59:00"
action:
- service: input_number.set_value
data:
entity_id: input_number.temp_day_before
value: "{{ states('input_number.temp_yday')|float(0.0) }}"
- service: input_number.set_value
data:
entity_id: input_number.temp_yday
value: "{{ states('sensor.current_temperature')|float(0.0) }}"
Thank you fot quick response!
I will try that now and post results when I’m back from vacation (I am about to go the airport).
You could manually trigger it a couple of times, which should populate both helpers with the current temperature. At least you’ll know that the automation works…
I’m using Average sensor from HACS to do that.
- platform: average
name: Temperature yesterday this time
unique_id: ANYUNIQUEID
entities:
- sensor.openweathermap_temperature
start: "{{ now() - timedelta(days=1, minutes=5) }}"
end: "{{ now() - timedelta(days=1, minutes=0) }}"
But if you want the temperature from exactly 23:59 that’s not the way.
sorry to bother with the original post from 2021.
My template sensor is working, but it becomes unknown after HA restart (i understand why) but trying to research your hint, HA Startup trigger for refreshing the value.
I’ve found
- platform: homeassistant
event: start
- platform: event
event_type: event_template_reloaded
but I’ve no success with combining them into a template… 