Sensor state 24 h ago

Configuration
1

I’m trying to figure out how to get a sensor state that was 24h ago. Is that possible at all?

2

Perhaps this?

3

Do you want to create a sensor that keeps showing continuously what e.g. the temperature was 24 hours ago so that you can compare it to the temperature now or is this just for one-offs?

I look into this a while ago and while I’m sure it can be done with a SQL sensor but I have not tackled it yet because I’m sure that it adds some strain on the database processes which is something I’m shying away from because I’m running my installation on a Pi 4.

So, no: unfortunately, I don’t have a solution for you but I’m following this thread to see if somebody else does :wink:

4

Yes, as @chairstacker mentioned you can do it using a SQL sensor. SQL sensors can be setup in the UI on the Integrations page. For the following, make sure to set “state” for the column value.

SELECT state from states 
where entity_id = 'sensor.YOUR_SENSOR' 
and last_updated_ts <= strftime('%s', 'now','-24 hours') 
order by last_updated_ts DESC LIMIT 1;

Keep in mind that, depending on the update frequency of your sensor and other factors, this may not be a 100% accurate reflection of the actual situation you are measuring. Since your database won’t necessarily have a value saved exactly 24 hours ago, it’s usually a good idea to use “>=” or “<=” to get one of the closest values.

EDIT: Updated for 2023.2 database schema update

1 Like
5

I use this:

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

Yours seems simpler.

6

That works perfectly. Thank you

1 Like
7

well that was working great until I updated my HA to the latest version v2023.2
Now in the log files I get:

Logger: homeassistant.components.sql.sensor
Source: components/sql/sensor.py:240
Integration: SQL (documentation, issues)
First occurred: 16:26:21 (47 occurrences)
Last logged: 16:49:21

SELECT state from states where entity_id = 'sensor.battery_charge' and last_updated <= datetime('now','-24 hours') order by last_updated DESC LIMIT 1; returned no results

I also tried executing that query in the SQLite Web addon and that is saying: Empty result set.

8

last_updated has gone in the v32 database schema: it’s last_updated_ts now which is a UNIX timestamp.

2 Likes
9

it would appear that the last_updated_ts is now stored in float where last_update was datetime so the correct query is:

SELECT state from states 
where entity_id = 'sensor.battery_charge' 
and last_updated_ts <= strftime('%s', 'now', '-1 day')
order by last_updated_ts DESC LIMIT 1;
10

Hmm, none of these solutions are working for me.

Mine (updated) returns unknown:

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

Drew’s returns “invalid” query when I try to submit it:

SELECT state from states where entity_id = 'sensor.total_cost_today' and last_updated_ts <= strftime('%s', 'now','-24 hours') order by last_updated_ts DESC LIMIT 1;

Andre’s returns “invalid” query when I try to submit it:

SELECT state from states where entity_id = 'sensor.total_cost_today' and last_updated_ts <= strftime('%s', 'now', '-1 day') order by last_updated_ts DESC LIMIT 1;

Screenshot 2023-02-05 at 21-48-09 Settings – Home Assistant

1
1613×793 25.8 KB

2
2631×796 28.2 KB

11

I suspect that this is because I use sqllite and your’s is by the looks of it mysql. Try something like this:

SELECT state FROM states
WHERE entity_id = 'sensor.total_cost_today'
AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC LIMIT 1;

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp

2 Likes
12

Thank you!

That works.

13

It would appear that the database was changed again in the 2023 April update, after some digging the new syntax that seems to be working fine is this:

SELECT state 
FROM states 
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.battery_charge' 
  AND last_updated_ts <= strftime('%s', 'now', '-1 day')
ORDER BY last_updated_ts DESC LIMIT 1;
3 Likes
14

So for my mysql sensor, this?

SELECT state FROM states
WHERE states_meta.entity_id = 'sensor.total_cost_today'
AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC LIMIT 1;
15

try this:

SELECT state 
FROM states 
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.total_cost_today' 
  AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC 
LIMIT 1;
1 Like
16

Thanks. That does indeed work. Much appreciated.

17

This is working also for me, much appreciated thank you verry much. !!

18

Hello,

this code gives me the following error:

SELECT state 
FROM states 
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.total_dc_power' 
  AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC 
LIMIT 1;

Error executing query SELECT state FROM states INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id WHERE states_meta.entity_id = 'sensor.total_dc_power' AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) ORDER BY last_updated_ts DESC LIMIT 1; : (sqlite3.OperationalError) near "1": syntax error [SQL: SELECT state FROM states INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id WHERE states_meta.entity_id = 'sensor.total_dc_power' AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) ORDER BY last_updated_ts DESC LIMIT 1; ] (Background on this error at: https://sqlalche.me/e/20/e3q8)

if I try something like this:

      SELECT state 
      FROM states 
      INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
      WHERE states_meta.entity_id = 'sensor.total_dc_power' 
      AND CAST(strftime('%s', last_updated) AS integer) > (CAST(strftime('%s', 'now') AS integer) - 86400)
      ORDER BY last_updated_ts DESC 
      LIMIT 1;

I get empty result set

does anyone know why? @alfwro13
thanks in advance!

19

After long trying I’ve got it working with the current HA version.
In my case I need to get the temperature of my pool of one hour ago (to calculate heating speed in °C / hour).
I’m using the MariaDB Addon.

sql:
  - name: Pool - Temperatur (vor 1 Stunde)
    unique_id: pool_temperatur_one_hour_ago
    query: >
      SELECT states.state FROM states WHERE metadata_id=(SELECT metadata_id FROM states_meta where entity_id='sensor.pool_wassertemperatur') and last_updated_ts < UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR) ORDER BY last_updated_ts DESC LIMIT 1;
    column: "state"
    unit_of_measurement: °C

To make it match the question, the INTERVAL can easily be changed to 1 DAY or whatever you need for your case.

20

Andre, any idea how to select only numeric states from this query?

SELECT state 
FROM states 
INNER JOIN states_meta ON states.metadata_id = states_meta.metadata_id
WHERE states_meta.entity_id = 'sensor.total_cost_today' 
  AND last_updated_ts <= UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)
ORDER BY last_updated_ts DESC 
LIMIT 1;