Get last_changed From Sensor When it Equaled "Something"

Configuration
1

Curious if it’s possible to get the last_changed info from a sensor, but not just when it changed, I want to know when it changed from a certain state.

Use case, I am tracking location in the house using ESPresence which is working great, however I want to know the last time an area was unoccupied. The way ESPresence works is it sets up a Sensor for my phone and then changes the state of it according to what it’s reading the closest ESP device. Works great, but I want to do something like…

When phone_bt last_changed from ‘foyer’
I can get the last_changed pretty easily, but how do I know when the last time it had a state = ‘foyer’

{{
  as_timestamp(now()) -
  as_timestamp(states.sensor.phone_bt.last_changed)
}}

And a bit off topic, but this would probably really help me in the future. There is documentation everywhere, but finding the info on what I would guess is ‘advanced’ templating, how do I find the different classes/attributes of things like ‘states.sensor…’?

2

Create a Trigger-based Template Sensor to record when that happens.

The other option is to use a SQL Sensor to extract that information from the database.

2 Likes
3

Awesome stuff! Your suggestion to use a SQL Sensor was spot on and worked like a charm until the recent HA update to the DB where the last_changed attribute was deprecated and replace with last_changed_ts which is in UnixEPOCH time. Once I dug into the documentation to find that change it was an easy fix. For anyone looking to do something similar to setup sensors to give when a room was last occupied feel free to use the below, just replace your entity_id in 2 spots and the state (which is room in my case for ESPresence). Keep in mind there is an extra bit of lag here as it is doing a DB query to update the sensor, so no time sensitive sensor can be setup with it, but for my needs the extra seconds don’t really matter.

I will be looking into the recent streaming data that was released in the most recent HA update, curious if there are any efficiencies to be gained using that over the SQL Sensor and if I can utilize in the same manner.

SELECT
    CASE WHEN COUNT(1) = 0
    THEN 'Occupied'
    ELSE datetime(last_changed_ts, 'unixepoch', '-6 hours')
   END AS YOUR_DESIRED_ATTRIBUTE_NAME
FROM "states" 
WHERE entity_id = 'sensor.YOUR_SENSOR_NAME'
AND state_id >
	(
		SELECT state_id FROM "states"
		WHERE entity_id = 'sensor.YOUR_SENSOR_NAME' AND state = 'YOUR_ROOM_NAME' AND last_changed_ts IS NOT NULL
		ORDER BY state_id DESC
		LIMIT 1
	)
AND last_changed_ts IS NOT NULL
ORDER BY state_id ASC
LIMIT 1