Sensor value for now() - 24h

Configuration
,
1

Hi,
I have temperature sensor sensor.online_thermometer_temperature, which holds temperature value from one of online services. History recorder for that sensor is enabled.
I want to create another sensor that would set its value based on average from (sensor.online_thermometer_temperature and sensor.online_thermometer_temperature value that was exactly 24 hours ago).
I need that value all day long so every time sensor.online_thermometer_temperature value changes or (in perfect solution) sensor.online_thermometer_temperature - 24h value changes i need updated average.
And here is my problem. I saw some examples based on SQL but nothing that i can use straightforward.
Is there any way to read that value from history other then SQL?
If SQL is the only way, how should i do it for MariaDB ?
Big thank you in advance for everybody willing to help.

2
3

Unfortunately if understand correctly Average Sensor for Home Assistant is not what i need.
I do not want to calculate average temperature for past 24h i need average form two distinct values now and now-24 hours, here is example:
I want to get temperature value for 7:00 AM today and average it with temperature that was at 7:00 AM yesterday.
Now i have sensor: (this is simplified version that not covers corner cases but works fine as example)

- sensor:
    - name: "recommended_indoors_temperature"
      unit_of_measurement: "°C"
      state: "{{(states('sensor.online_thermometer_temperature')|float|round(0)) * 0.1 + 20}}"

and i want to replace sensor.online_thermometer_temperature with average from current sensor.online_thermometer_temperature value and value that was 24 hours ago. (to smooth sudden weather changes effect)

So in perfect solution I need that “template” listens for the following state changed events: sensor.online_thermometer_temperature and sensor.online_thermometer_temperature 24 hours ago value
But if i could listen to only sensor.online_thermometer_temperature changes for average update that still will be acceptable.
What is mandatory: every time sensor.online_thermometer_temperature value changes i have to average it with value from the same sensor 24 hours ago.

4

not sure if it helps, but if it is just about displaying the data you could use Apex Charts to display the curve for now to now-24h and on the same chart another curve for now-24h to now-48h.
So in essence you get two curves, one 1 for the last 24 hours and another parallel for the same 24hours on the day before.
See:
GitHub - RomRider/apexcharts-card: 📈 A Lovelace card to display advanced graphs and charts based on ApexChartsJS for Home Assistant).%20See%20statistics-,offset,-string

What I use when I need to store a certain value for an amount of time is to use the var extension from HACS.

here is an example:

ups_charger_last_power_consumption:
  friendly_name: "UPS Charger Last Power Consumption"
  force_update: false
  restore: true
  unit_of_measurement: "W"
  value_template: "{{ float(states('sensor.ups_charger_power'),320) }}"

So every time I call the Service var.update with this Variable as entity it will execute the value_template and store this as value for the variable.

image
image1036×159 15.5 KB

Hope this helps. :slight_smile:

5

That still is not what i need, any other ideas?

6

Not really.

I’m no SQL ninja and I don’t use MariaDB, but I think the following might work to get the value 24hrs ago.

SELECT states.state FROM states 
WHERE metadata_id=(
  SELECT metadata_id FROM states_meta 
  WHERE entity_id='sensor.online_thermometer_temperature') and 
  last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 24 HOUR) 
ORDER BY last_updated_ts 
DESC LIMIT 1

Then you can use a template sensor to get the average:

template:
  - sensor:
      - name: Online Therm Temp Average
        state: >
          {% set ents = ('sensor.online_thermometer_temperature', 
          'sensor.online_thermometer_temperature_24hr') | select('has_value') %}
          {{ average(expand(ents)|map(attribute='state')|map('float')) }}
        availability: >
          {{ has_value('sensor.online_thermometer_temperature') or has_value('sensor.online_thermometer_temperature_24hr') }}
7

Hi. Finally i have solution that is described here: https://community.home-assistant.io/t/state-of-an-entity-sensor-x-time-ago/681370

1 Like