Help with the SQL integration to show last night min, max and average temperature

Configuration
1

Pretty new to home assistant and looking for a bit of help.

I have a sensor “sensor.temperature_sensor_harrys_room_temperature” where I’m looking to find the previous nights minimum, maximum and average temperature, from 7:00PM to 7:00AM.

Reading up on the best way of doing this, looks like the SQL integrations the best option. With a bit of ChatGPT I manged to get this far, but suspect the columns might be incorrect as not sure how HA stores this. Currently getting “SQL Query invalid”

Any help appreciated!

column
minimum

select query

SELECT MIN(temperature) AS lowest_temperature
FROM temperature_readings
WHERE sensor = 'sensor.temperature_sensor_harrys_room_temperature'
  AND timestamp >= CURDATE() - INTERVAL 1 DAY + '19:00:00'
  AND timestamp < CURDATE() + '07:00:00';
2

Just for any one who stumbles across this, manged to sort myself using info from THIS thread.

I used the SQL plugin an the query below, which seems to work in HA version 2023.11.2 :

Column:
state

This query shows the maximum temperature from the previous day, between 7:00PM and 7:00AM

SELECT state
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.temperature_sensor_harrys_room_temperature'
  AND last_updated_ts >= strftime('%s', 'now', '-1 day', '19:00:00')  -- 7:00 PM yesterday
  AND last_updated_ts < strftime('%s', 'now', '07:00:00')  -- 7:00 AM today
ORDER BY CAST(state AS DECIMAL(10,2)) DESC
LIMIT 1;

and this to show the minimum

SELECT state
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.temperature_sensor_harrys_room_temperature'
  AND last_updated_ts >= strftime('%s', 'now', '-1 day', '19:00:00')  -- 7:00 PM yesterday
  AND last_updated_ts < strftime('%s', 'now', '07:00:00')  -- 7:00 AM today
ORDER BY CAST(state AS DECIMAL(10,2)) ASC
LIMIT 1;
3

Still stuck on using a query to return a timestamp of when the minimum or maximum temperature occurred?

4

In case anyone stumbles across this post in the future, these querys seem to work. Full disclosure, I don’t really know what I’m doing!

I changed (thanks ChatGPT) the query to filter out unwanted states as this was causing the entity to show as ‘unavailable’ in home assistant.

I also added the time stamp of when the temperature was as an attribute.

Shows the maximum temperature from the previous day, between 7:00PM and 7:00AM

SELECT state, datetime(last_updated_ts, 'unixepoch', 'localtime') AS last_updated_datetime
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.temperature_sensor_harrys_room_temperature'
  AND last_updated_ts >= strftime('%s', 'now', '-1 day', '19:00:00')  -- 7:00 PM yesterday
  AND last_updated_ts < strftime('%s', 'now', '07:00:00')  -- 7:00 AM today
  AND state NOT LIKE '%unavailable%' -- Exclude non-numeric values
  AND state NOT LIKE '%unknown%' -- Exclude non-numeric values
ORDER BY CAST(state AS DECIMAL(10,2)) DESC
LIMIT 1;

and this shows the minimum

SELECT state, datetime(last_updated_ts, 'unixepoch', 'localtime') AS last_updated_datetime
FROM states
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.temperature_sensor_harrys_room_temperature'
  AND last_updated_ts >= strftime('%s', 'now', '-1 day', '19:00:00')  -- 7:00 PM yesterday
  AND last_updated_ts < strftime('%s', 'now', '07:00:00')  -- 7:00 AM today
  AND state NOT LIKE '%unavailable%' -- Exclude non-numeric values
  AND state NOT LIKE '%unknown%' -- Exclude non-numeric values
ORDER BY CAST(state AS DECIMAL(10,2)) ASC
LIMIT 1;
5

I’d suggest setting something up to record this instead of trying to fish it out of the database after the fact. A set of statistics sensors to measure and trigger-based template sensors to “hold” is one option:

sensor:
  - platform: statistics
    name: "Harrys room temperature max"
    entity_id: sensor.temperature_sensor_harrys_room_temperature
    state_characteristic: value_max
    max_age:
      hours: 12

  - platform: statistics
    name: "Harrys room temperature max timestamp"
    entity_id: sensor.temperature_sensor_harrys_room_temperature
    state_characteristic: datetime_value_max
    max_age:
      hours: 12

  - platform: statistics
    name: "Harrys room temperature min"
    entity_id: sensor.temperature_sensor_harrys_room_temperature
    state_characteristic: value_min
    max_age:
      hours: 12

  - platform: statistics
    name: "Harrys room temperature min timestamp"
    entity_id: sensor.temperature_sensor_harrys_room_temperature
    state_characteristic: datetime_value_min
    max_age:
      hours: 12

template:
  - trigger:
      - platform: state
        entity_id: sensor.harrys_room_temperature_max
    sensor:
      - name: Harrys room last night max
        state: "{{ trigger.to_state.state if now().hour >= 19 or now().hour < 7 else this.state }}"
        device_class: temperature
        unit_of_measurement: "°C"

  - trigger:
      - platform: state
        entity_id: sensor.harrys_room_temperature_max_timestamp
    sensor:
      - name: Harrys room last night max timestamp
        state: "{{ trigger.to_state.state if now().hour >= 19 or now().hour < 7 else this.state }}"

  - trigger:
      - platform: state
        entity_id: sensor.harrys_room_temperature_min
    sensor:
      - name: Harrys room last night min
        state: "{{ trigger.to_state.state if now().hour >= 19 or now().hour < 7 else this.state }}"
        device_class: temperature
        unit_of_measurement: "°C"

  - trigger:
      - platform: state
        entity_id: sensor.harrys_room_temperature_min_timestamp
    sensor:
      - name: Harrys room last night min timestamp
        state: "{{ trigger.to_state.state if now().hour >= 19 or now().hour < 7 else this.state }}"
1 Like
6

Thanks, thinking something like that may be easier.

I did look at using a statistics sensor, gets a bit tricky as needs to be after 7:00PM the previous day and before 7:00AM the current day.

Was thinking of using helpers instead and using automations to record. Just thought querying the original data was ‘neater’ but turning out to be quite difficult.

7

That’s basically what my suggestion above is doing, except with trigger-based template sensors instead of helpers and automations.

1 Like
8

Apologies, gotcha. Yes, probably a better way to do it thanks!

I’ve added to my configuration.yaml but get the error:

Logger: homeassistant.config
Source: config.py:609
First occurred: 4:44:44 PM (4 occurrences)
Last logged: 4:44:44 PM

Invalid config for 'template': 'state' is an invalid option for 'template', check: state Invalid config for 'template': required key 'entity_id' not provided
9

Oops, my mistake from hand-writing code. Fixed above — change the state: in each trigger block to entity_id:.