Get temperature from yesterday

Configuration
43

Yeah I’m using MariaDB. I think I’ve got it:

SELECT state FROM states WHERE entity_id = 'sensor.total_cost_today' AND HOUR(TIMEDIFF(UTC_TIMESTAMP(), last_updated))>=24 ORDER BY last_updated DESC LIMIT 1
1 Like
45

I started using Grafana for this (and other reasons).
It’s strange that nobody mentions the existence of it?

EDIT: this is mainly addressed to those looking for a way to have data from the past

46

I needed a sensor not a graph.

47

The querying of the DB indeed takes a lot of CPU power; my Pi4 increased from 30% CPU usage to 50%, which really slows the overall experience of HA.

Solution: disable the updates for the SQL sensor and update it via an automation at the frequency you want.

see: SQL - Home Assistant scan interval
In summary: disable the updates in the SQL integration (via the 3 dots and system options).
Next: make an automation to update the sensor, eg. every 10 minutes.

Example:

- alias: update van zonneproductie gisteren op dit uur
  id: "6f18655a-e923-46b5-9a83-8d3340458e83"  
  description: ""
  trigger:
    - platform: time_pattern
      minutes: /10
  condition: []
  action:
    - service: homeassistant.update_entity
      data: {}
      target:
        entity_id: sensor.zonneproductie_gisteren_op_dit_uur
  mode: single

Note: I defined the SQL sensor in the configuration.yaml file and saw that the sensor was mentioned 2 times in the SQL integration. I have the impression that you have to define the SQL sensor via the GUI only instead of the configuration.yaml because you cannot disable the updates via the config file.

48

Anyone could help?

I need to make query to show sensor state from 23:59 form yesterday and 23:59 from the day before yesterday.

I tried the queries given above but looks like my scropt is wrong as the integration doesn’t accept the query.

Thank you!

49

Please do not cross post on multiple threads. If you have a question, create a single new topic.

50

The timestamp spec in the database has changed in recent schema, so old SQL queries may no longer work. If you can wait a day to start using your data, I’d suggest using an automation to record the temperature in advance. Another option would be a trigger-based template sensor, but I’m not convinced they would do what you want if the system restarted. Something like this, with two input_number helpers already set up:

alias: Temperature recorder
description: >
  Records the temperature at 23:59 into input_number.temp_yday, dropping
  the previous value into input_number.temp_day_before.
trigger:
  - platform: time
    at: "23:59:00"
action:
  - service: input_number.set_value
    data:
      entity_id: input_number.temp_day_before
      value: "{{ states('input_number.temp_yday')|float(0.0) }}"
  - service: input_number.set_value
    data:
      entity_id: input_number.temp_yday
      value: "{{ states('sensor.current_temperature')|float(0.0) }}"
2 Likes
51

Thank you fot quick response!

I will try that now and post results when I’m back from vacation (I am about to go the airport).

52

You could manually trigger it a couple of times, which should populate both helpers with the current temperature. At least you’ll know that the automation works…

53

I’m using Average sensor from HACS to do that.

- platform: average
  name: Temperature yesterday this time
  unique_id: ANYUNIQUEID
  entities:
    - sensor.openweathermap_temperature
  start: "{{ now() - timedelta(days=1, minutes=5) }}"
  end: "{{ now() - timedelta(days=1, minutes=0) }}"

But if you want the temperature from exactly 23:59 that’s not the way.

54

sorry to bother with the original post from 2021.
My template sensor is working, but it becomes unknown after HA restart (i understand why) but trying to research your hint, HA Startup trigger for refreshing the value.
I’ve found

  - platform: homeassistant
    event: start
  - platform: event
    event_type: event_template_reloaded

but I’ve no success with combining them into a template… :confused: