I’d like to list the lowest temperature seen during the night (2300-0700) and present it to the user (me) at 0700.
I’ve read multiple posts here but I haven’t seen a working configuration yet. I had previously hacked together a solution in node red which basically queried the recorder API and then just extracted the lowest value, but I’d like to redo this in Home Assistant native (due to reasons).
I tried putting together a SQL query to use a SQL sensor, but sqlite proved to require more and more workarounds so I eventually gave up, as the results were also not correct (probably my query being in the wrong, tho).
Hi, you can use the statistics card to quickly set up using your sensor, and set it to show the min temp for the day. In this case it would be from midnight, but you could adjust the timespan via YAML i assume.
If you wanted to use the value in automations or send via notification, you would have to use the Statistics Integration. I haven’t set this up myself, but it seems to be pretty straightforward through the docs.
This is basically what I use, you can set the reset time to whatever you want. Where I live it is pretty rare for the lowest temp to occur prior to midnight, so that’s what I use
template:
- trigger:
- platform: time
at: "00:00"
id: reset
- platform: state
not_to:
- unknown
- unavailable
entity_id:
- sensor.outdoor_temp_1
- sensor.outdoor_temp_2
sensor:
- name: Outside Temperature Daily Min
unit_of_measurement: "°F"
unique_id: outside_temp_daily_min_001
state: |
{% set source =
('sensor.outdoor_temp_1', 'sensor.outdoor_temp_2')
| select('has_value') | map('states')
| select('is_number') | map('round', 1) | min %}
{% if trigger.id == 'reset' %}
{{ source }}
{% else %}
{% set current = (this.state or trigger.to_state.state) | float(source) %}
{{ [source, current] | min }}
{% endif %}
I’m no SQL expert, but I think the following should work if you’re only dealing with a single sensor and really only interested in the value for your morning announcement, a hacky SQL query would be:
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.outside_temp'
AND last_updated_ts >= strftime('%s', 'now', '-8 hours')
ORDER BY
CAST(states.state as numeric) ASC
LIMIT 1;
Or, for the defined time span:
SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.outside_temp'
AND last_updated_ts >= strftime('%s', 'now', 'start of day', '-1 hours')
AND last_updated_ts <= strftime('%s', 'now', 'start of day', '+7 hours')
ORDER BY
CAST(states.state as numeric) ASC
LIMIT 1;
start of day is UTC so you need to adjust the hours values by adding the inverse of your timezone offset.
EDIT: Updated SQL queries to convert state strings to numbers.
Thank you very much! I’m going to start with your template sensor even though it’s not exactly what I had in mind.
I tried the SQL sensors and they actually didn’t return the correct results, which is intriguing.
[core-ssh config]$ sqlite3 home-assistant_v2.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> SELECT
states.state
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
states_meta.entity_id = 'sensor.temp_sensor_1_temperatur_garage_utomhus_ds18b20'
AND last_updated_ts >= strftime('%s', 'now', '-8 hours')
ORDER BY
states.state ASC
LIMIT 1;
-1.4
If I look at the graph for the sensor, the lowest temperature the last 8 hours (I’m in UTC+1) it should have returned something like -7.6 C whereas -1.4 C is the latest value.
When I tried constructing the query myself (very similar to yours, but I think I used a subquery instead of a join due to reasons) I noticed similar issues. I also had a lot of issues with float validation so I gave up…
I like the trigger-based template solution. I just recently started experimenting with those and they have the potential to really cut down on the number of automations I use, along with those templates I define just to extract a state from attributes of other sensors.
Thanks for posting that example. It can be a real struggle figuring out what does and doesn’t work with these trigger-based templates.
In case anyone else is looking for something similar, this is how I did it!
SQL query, replace the sensor(s) in the WHERE clause with your own:
SELECT
states.state,
states.last_updated_ts AS last_updated_ts
FROM
states
INNER JOIN states_meta ON
states.metadata_id = states_meta.metadata_id
WHERE
(
states_meta.entity_id = 'sensor.temp_sensor_1_temperatur_garage_utomhus_ds18b20'
OR states_meta.entity_id = 'sensor.bedroom_temperature_2'
)
AND last_updated_ts >= strftime('%s', 'now', 'start of day', '-1 hours')
AND last_updated_ts <= strftime('%s', 'now', 'start of day', '+7 hours')
ORDER BY
CAST(states.state as numeric) ASC
LIMIT 1;
Copy the query, go to Home Assistant. Add an integration, search for SQL.
In the Settings popup:
Database URL, leave blank to use the default database
Column, type state
Query, paste the above
Unit of measurement, either °C or °F but this field is optional
State class, set to measurement to keep it long-term
Press save.
This will now update every 30s by default, which is a bit wasteful but not terrible. If you want to make this less wasteful, you can disable auto polling for the integration and build an automation that updates the entity (service homeassistant.update_entity) when you want it to - but that is left as an excerise for the reader.
