I recently decided to only use Home Assistant primitives to manage my ‘smart’ home, where I previously also used Node Red for automations. Since I was redoing a few things I decided to make them better this time around - like my critical automation to each morning notify me what the lowest temperature was during the night!

I also cheated a bit, and went straight for the database instead of trying anything else. (-:

I got some help (as in someone else helped me write the query) writing this query, as my initial attempt fell short. The query just fetches the lowest temperature seen between 2300 and 0700, by the sensors specified. That final CAST is important.

SELECT
  states.state,
  states.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;

I then created a new SQL integration, with the settings below (the screenshot is in Swedish but should be parseable):

One detail to notice is that I’ve set state class to measurement. This will, afaik, allow the values to be stored longer than the default retention period. Another thing to notice is that I’ve included last_updated_ts so I can know when temperature was at it’s lowest point.

Then, I’ve disabled auto polling for this integration. Otherwise this query would have run every 30 seconds, while it only needs to run once a day. No, this isn’t premature optimization……..

To update the sensor and send me a notification, I have an automation that looks something like this:

- id: '123123123123'
  alias: Lowest temperature during night
  description: ''
  trigger:
  - platform: time
    at: 07:03:00
    id: update_trigger
  condition: []
  action:
  - if:
    - condition: trigger
      id:
      - update_trigger
    then:
    - service: homeassistant.update_entity
      metadata: {}
      data: {}
      target:
        entity_id: sensor.coldest_during_night
    - service: notify.matrix_notify
      metadata: {}
      data:
        # Should look like this: "Lägsta temperatur inatt: -7.6 (04:58:55)"
        message: |
        Lägsta temperatur inatt: {{ states.sensor.coldest_during_night.state }} ({{ state_attr(''sensor.coldest_during_night'', ''last_updated_ts'') | int | timestamp_custom(''%H:%M:%S'') }})        

Now we just have to wait til the morning to see if this works. :-)