I’ve been searching far and wide to see if there is a built in way to get the time since an entity was last in a certain state. I’ve tried to see if there were any built in template functions that could query an entity’s history to find it, but that functionality doesn’t seem to exist.
To disclose my use case, I have notifications to know when persons come and go from my home. Most users do not have the app, and do not share their location with home assistant, and they are only tracked when they connect to the wifi. In those cases, it’s very easy to take the trigger.from_state.last_changed and use the relative_time template function to get a nice string like “So and so was gone/home for 40 minutes” because the only two states they can be in are home and not_home.
However, for my wife and me, when we enter zones, the state can change to the name of that zone. So if I go to work for the day and my location state changes to my employer’s name, and then take 10 minutes traveling home, when I arrive home the notification would say I was only away for 10 minutes, and not the more accurate 8+ hours or so I was actually away from home. If I could find a way to get the timestamp of the last time the state changed from home/not_home to any other state, I could make a more accurate notification.
Is anyone aware of an easier way to do this besides creating a template/trigger sensor that updates when a person’s state changes betwen home and not_home?
Have you tried a SQL sensor? it may be capable of getting the info you need.
as an example of a yaml I created some years ago, trying to troubleshoot a faulty amcrest camera ( that got replaced later) (I don’t use this sensor anymore)
- platform: sql #SQL sensor
scan_interval: 120
queries:
- name: HASS Restarted by amcrest last 24h
#query time examples https://stackoverflow.com/questions/1888544/how-to-select-records-from-last-24-hours-using-sql
query: "SELECT COUNT(DISTINCT state) AS count FROM states WHERE entity_id='counter.hass_restarted_by_amcrest' AND state!='unknown' AND datetime(last_changed) BETWEEN datetime('now', '-1 day') AND datetime('now');"
column: 'count'
to make that work, I had an automation that restarted HA when tge camera disconnected (workaround solution back then) and then a counter that incremented every restart. this sql sensor would inform me how many time that happened in the last 24h.
I think what you want is doable, just need to find the right sql query string for your goal
Maybe something a bit like that:
(totally UNTESTED, probably broken, just an idea)
- platform: sql
scan_interval: 120
queries:
- name: Previous time that state was home
query: "SELECT * FROM (SELECT * FROM states WHERE entity_id = 'person.you' AND state='home' ORDER BY state_id DESC LIMIT 2) two_entity ORDER BY state_id ASC LIMIT 1;"
column: 'last_changed'
I do something similar using node red, for a different reason, but it would do the same thing for you. It watches for a sensor to change to a specific state, sets the time, then updates a node red sensor in HA. The trick is that it is looking for a change to a specific state before it updates.
This is really an ideal use case for a trigger-based template sensor. Not sure why the OP wanted to avoid it.
template:
- trigger:
- platform: state
entity_id:
- person.john_doe
to:
- home
from:
- home
sensor:
- name: "John Doe Last Home"
unique_id: e80380db-5261-457e-bd92-1b63381fcd49
device_class: timestamp
state: "{{ now() }}"
Heh, I don’t think OP was aware of the trigger-based template sensor solution at the time, so he asked here. That does seem like it could work. If my understanding of that solution is correct, each time the entity transitions from the home state or two the home state, it’ll basically put a timestamp in the history of when that transition occurred, yes? If so, yeah this might be it.
I only said that because what you seemed to want to avoid was the exact solution I provided
The code above will create a trigger-based template sensor that updates when a person’s state changes to home or from home. (Essentially, when either entering or leaving home.) The state of the sensor will simply be the time when the sensor last changed.
In your automation you can either refer to the sensor’s state or the sensor’s last_changed property because they should match (other than the former being a string and the latter being a datetime object).
Apologies if my reply came off as flippant. I honestly don’t remember why I didn’t want to use that solution before; I feel like I asked this ages ago. But this definitely seems like a valid solution.
Is this true? I’m just learning of trigger templates also, and this is just what I need. But unless I’m wrong, in an automation this trigger would only fire if you are going “from home to home” which is probably never.
This is great, quite in line with what I’m looking for. Question: is it possible to set trigger based templates via the GUI or strictly through YAML? I can’t find the option on the GUI and want to double check. Thanks.