How to create an entity that holds the max temperature of the last 24 hours?

Configuration
1

Hello All,

I’m trying to have an entity that holds the maximum temperature from the past 7 or so days to use in my irrigation process. The thought is to ultimately increase watering duration/events during extreme temperatures.

I’m testing on Home Assistant 2022.5.5. Initially I have created an SQL sensor that runs once a day and the query returns the maximum value from the outside temerature entity for the previous X hours. I am running the MariaDB addon with recorder setup for that database. I disabled polling for the sensor and created an automation that updates the entity once per day. That said, I found that this sensor/ integration isn’t reliable and frequently, but not always, returns an error on execution (MySQLdb._exceptions.OperationalError) (2006, ‘MySQL server has gone away’). No other DB errors are logged or occurred before creating that sensor.

Rather than going down the rabbit hole of troubleshooting the error (the SQL integration in UI is fairly new) I’ve decided to ask the community if there is a better way.

Has anyone solved this specific problem before? Thanks for any suggestions.

2

Use a statistics sensor.

3

I don’t know if this will help, but I am using the statistics platform to track the min and max freezer temperature over 48 hours.

In sensors.yaml

#Min/Max Sensors
# https://www.home-assistant.io/integrations/statistics/
#      
#Freezer minimum and maximum temperatures      
  - platform: statistics
    name: "Freezer min"  
    entity_id: sensor.freezer_temperature_c
    state_characteristic: value_min
    sampling_size: 20000
    max_age:
      hours: 48
    
  - platform: statistics
    name: "Freezer max"  
    entity_id: sensor.freezer_temperature_c
    state_characteristic: value_max
    sampling_size: 20000
    max_age:
      hours: 48
4

It’s not clear to me how often this sensor updates. I just want one update per day with the maximum value. Will the statistics sensor do this?

5

my sensor.freezer_temperature_c updates every ten seconds (it is an ESPHome device). You control how often the source temperature sensor polls.

6

I think you’re stuck with troubleshooting the SQL sensor if you want to limit it to a single run each day.

7

You can set this with the sampling_size of a statistics sensor.

Take a look here: Statistics - Home Assistant

8

I don’t think that will work if the goal is to drive the value based on a max age of 24 hours.

9

You can use a statistics sensor like Stephan is showing above, probably the easiest way. That being said you should be aware that statistics sensors reset at restart. It will either show you the maximum value since 7 days ago OR since the last restart of HA, whichever datetime is later.

Wanted to point that out to you since I assume that’s different then how your SQL sensor works. It’s unlikely your SQL query is affected by HA restart.

If this bothers you I’m not sure there’s a good alternative to an SQL sensor. You might just have to turn down the log level for that component.

10

I guess I didn’t provide enough details, for the sensor I’m trying to create, I’d like to find the average of the maximum temperature for 4-5 days. In other words if it’s over 100 degree for more than 4-5 days in a row, then change the irrigation schedule. That’s why I wanted one value per day.

For the SQL sensor, it’s not just that it logs an error, it doesn’t update when the error occurs, which makes it pretty useless in my case. I’ve tried changing the time it executes as well as lowering the count of the dataset the query runs against but it still randomly errors.

11

Wild thought, how about an automation that updates an input number only if the current sensor value is greater than the current input number value, then use the input number value as the trigger for your irrigation automation if it’s above 100 for x amount of time in your case 4-5 days, then reset the input number?

12

I do something similar to what coolie101 suggested above.

I use the hass-variables custom integration but I think you could do the same thing using an input number except I also record the time as an attribute that the input_number can’t do.

then I save that data every day to another variable so I have a daily history.

here are the variables:

variable:
  lowest_temp_so_far:
    value: '100.0'
    restore: true
    attributes:
      icon: 'mdi:thermometer'
      unit_of_measurement: '°F'
      time: '00:00:00'
      friendly_name: 'Todays Lowest Temperature So Far'
  highest_temp_so_far:
    value: '0.0'
    restore: true
    attributes:
      icon: 'mdi:thermometer'
      unit_of_measurement: '°F'
      time: '00:00:00'
      friendly_name: 'Todays Highest Temperature So Far'
  lowest_temp_history:
    value: '55.0'
    restore: true
    attributes:
      icon: 'mdi:thermometer'
      unit_of_measurement: '°F'
      date: '1971-01-01'
      friendly_name: 'Actual Lowest Temperature History'
  highest_temp_history:
    value: '85.0'
    restore: true
    attributes:
      icon: 'mdi:thermometer'
      unit_of_measurement: '°F'
      date: '1971-01-01'
      friendly_name: 'Actual Highest Temperature History'

here are the automations:

  - alias: Env Set Lowest Temp So Far
    trigger:
      - platform: state
        entity_id: sensor.dark_sky_temperature
      - platform: time
        at: '00:00:00'
    action:
      - choose:
          - conditions:
              condition: template
              value_template: "{{ trigger.platform == 'time' }}"
            sequence:
              service: variable.set_variable
              data:
                variable: lowest_temp_so_far
                value: "{{ states('sensor.dark_sky_temperature') | float }}"
                attributes:
                  time: "{{ states('sensor.time') }}"
        default:
          service: variable.set_variable
          data:
            variable: lowest_temp_so_far
            value: >
              {% if states('sensor.dark_sky_temperature') not in ['unavailable', 'unknown'] and (states('sensor.dark_sky_temperature') | float < states('variable.lowest_temp_so_far') | float) %}
                {{ states('sensor.dark_sky_temperature') | float }}
              {% else %}
                {{ states('variable.lowest_temp_so_far') }}
              {% endif %}
            attributes:
              time: >
                {% if states('sensor.dark_sky_temperature') not in ['unavailable', 'unknown'] and (states('sensor.dark_sky_temperature') | float < states('variable.lowest_temp_so_far') | float) %}
                  {{ states('sensor.time') }}
                {% else %}
                  {{ state_attr('variable.lowest_temp_so_far', 'time') }}
                {% endif %}
                                
  - alias: Env Set Highest Temp So Far
    trigger:
      - platform: state
        entity_id: sensor.dark_sky_temperature
      - platform: time
        at: '00:00:00'
    action:
      - choose:
          - conditions:
              condition: template
              value_template: "{{ trigger.platform == 'time' }}"
            sequence:
              service: variable.set_variable
              data:
                variable: highest_temp_so_far
                value: "{{ states('sensor.dark_sky_temperature') | float }}"
                attributes:
                  time: "{{ states('sensor.time') }}"
        default:
          service: variable.set_variable
          data:
            variable: highest_temp_so_far
            value: >
              {% if states('sensor.dark_sky_temperature') not in ['unavailable', 'unknown'] and (states('sensor.dark_sky_temperature') | float > states('variable.highest_temp_so_far') | float) %}
                {{ states('sensor.dark_sky_temperature') | float }}
              {% else %}
                {{ states('variable.highest_temp_so_far') }}
              {% endif %}
            attributes:
              time: >
                {% if states('sensor.dark_sky_temperature') not in ['unavailable', 'unknown'] and (states('sensor.dark_sky_temperature') | float > states('variable.highest_temp_so_far') | float) %}
                  {{ states('sensor.time') }}
                {% else %}
                  {{ state_attr('variable.highest_temp_so_far', 'time') }}
                {% endif %}
                
  - alias: Env Set Actual Daily Lowest Temp
    trigger:
      - platform: time
        at: '23:59:00'
    action:
      - service: variable.set_variable
        data:
          variable: lowest_temp_history
          value: "{{ states('variable.lowest_temp_so_far') }}"
          attributes:
            date: "{{ states('sensor.date') }}"
                              
  - alias: Env Set Actual Daily Highest Temp
    trigger:
      - platform: time
        at: '23:59:00'
    action:
      - service: variable.set_variable
        data:
          variable: highest_temp_history
          value: "{{ states('variable.highest_temp_so_far') }}"
          attributes:
            date: "{{ states('sensor.date') }}"

it’s a bit long winded but it works for my needs.

13

Input number wouldnt work. If the value hadnt increased in 5 days then you’d have a problem. The value is no longer the max in the past 5 days since it’s older then that. And you have no idea what the next highest value is since you aren’t tracking that.

What finity showed works though since you still have the history. Although come to think of it you can also just do that with a single trigger template sensor since those restore state now.

1 Like
14

Try the ha-average integration. You can specify a date range like the last 7 days as you want and will provide an average, min and max. I do a similar thing in my own project to get a five day moving average temperature for a similar purpose. See here for the implementation.

1 Like
15

Problem?, isn’t the requirement the highest value over that period of time?, so if it’s 20 on Mon, 25 on Tues, and 15 the rest of the week, then you’ll still have the highest value?

16

Right, and then? What happens when you hit the following Monday? If you had a sensor which was “highest value in the past 5 days” and your readings were this:
6/1 - 20
6/2 - 25
6/3 - 15
6/4 - 16
6/5 - 15
6/6 - 14
6/7 - 15
6/8 - 14

Then the input number approach breaks on 6/8. By those readings the sensor should have value 20 on 6/1, 25 on 6/2 - 6/7 and then 16 on 6/8. Because on 6/8 the 25 reading is more then 5 days old so it is no longer “the highest value in the past 5 days”. The highest reading in the past 5 days on 6/8 is 16 which occurred on 6/4.

See the problem here? If you only track the highest value it only works if the value keeps going up. As soon as the value peaks for 5+ days that approach breaks.

17

Got it, I assumed the tracking required was for a 5 day period, then reset, then start over for the following 5 days.

18

Thanks for all the input everyone. I’m still pondering the best path forward. I’ve seen the solution from @finity and it seems to be closest to my needs out of the box.

19

There are probably other good solutions suggested but I found the statistics sensor did what I needed. I marked @stevemann as the solution as the second example he posted is effectively what I needed. Adjust the sampling_size and max_age appropriately. Keep in mind sampling_size needs to be larger than the daily number of updates from the original entity_id.

20

Hi
How to do it for the day from midnight to midnight, and not for the last 24 hours?
I want to show the minimum and maximum temperature of the day.