Presence detection - Calculate time away and set as variable?

Configuration
1

I have a Telegram integration that notifies me when someone has entered or left the home using a bayesian binary sensor. Notifications current look like, “Sarah has left the house” and “Sarah has entered the house” when the state changes to “home” and “away” on the binary sensor.

But is there some way to calculate the time away, and return that as a variable? It would be cool to have something like, “Sarah has returned to the house, after being away for 1 hour and 5 minutes.” Or even …“for 65 minutes.”

2

you could use an input datetime to store the time and date when the sensor changes to away then compare that time to the time the sensor returns then put the result of that comparison into the notification.

3

My gut reaction was along the same lines as @finity’s suggestion. However, I’d use an input_number and have an automation that triggers when the person leaves (i.e., the device_tracker changes from: 'home') and stores {{ as_timestamp(now()) }} to the input_number (because input_datetime’s, IMHO, have a fatal flaw in the way you set them – you can’t set both date & time in one template, so there’s always the possibility that the date gets set right before midnight, and the time gets set right after midnight. :frowning:) Then when the person comes home you can use {{ states('input_number.xxx_left_home')|float|timestamp_local }}, for example, in your message.

But this whole technique has its own flaw. I.e., if the person leaves while HA is down, then the saved value would be wrong at best.

Probably a better way is to query the recorder database. I’ve been wanting an excuse to try that via the SQL Sensor. This presented a fun exercise! :slight_smile: Anyway, I came up with the following:

sensor:
  - platform: sql
    db_url: !secret db_url
    queries:
      - name: Xyz left home last
        query: "SELECT last_changed FROM states WHERE entity_id = 'device_tracker.xyz' and state_id > (SELECT state_id FROM states WHERE entity_id = 'device_tracker.xyz' and state = 'home' and state_id < (SELECT state_id FROM states WHERE entity_id = 'device_tracker.xyz' and state != 'home' ORDER BY state_id DESC LIMIT 1) ORDER BY state_id DESC LIMIT 1) ORDER BY state_id LIMIT 1;"
        column: last_changed

This is really three queries in one. First it searches backwards looking for the most recent state that isn’t ‘home’. (This represents the time right before the person came home.) Then it looks backwards from there for the first state that is ‘home’. (That represents the last time the person was last home.) Lastly it gets the next state after that, which is the first state that is not ‘home’ after the last time the person was ‘home’, which contains the time the person last left home before coming home. :slight_smile:

Of course this also isn’t perfect and wouldn’t work if the times being queried for aren’t in the database (e.g., person last left home longer ago than the db purge period.) But, I would think for most cases it might work well. Also, unfortunately the query can’t be calculated dynamically via a template; it has to be fully hard-coded. So, if you want to do this for multiple people, you’d have to have one per person.

I tried it on myself, and sure enough, it found the time I left home this morning before getting back this afternoon.

2 Likes
4

show off…:wink:

1 Like
5

BTW, like any polling sensor, the default is to update every 30 seconds. That means an SQL sensor like I suggest above will query the database every 30 seconds. For this use case, that’s probably not the best. So I’d suggest setting its scan_interval to a really high value, and then force it to update when you need via the homeassistant.update_entity service (e.g., in your automation that sends the notification, right before actually sending the notification. You might also need a slight delay between the two to give the sensor time to update.)

FWIW, here’s what I changed my test to:

sensor:
  - platform: sql
    scan_interval:
      days: 7
    db_url: !secret db_url
    queries:
      - name: Xyz left home last
        query: "SELECT last_changed FROM states WHERE entity_id = 'device_tracker.xyz' and state_id > (SELECT state_id FROM states WHERE entity_id = 'device_tracker.xyz' and state = 'home' and state_id < (SELECT state_id FROM states WHERE entity_id = 'device_tracker.xyz' and state != 'home' ORDER BY state_id DESC LIMIT 1) ORDER BY state_id DESC LIMIT 1) ORDER BY state_id LIMIT 1;"
        column: last_changed

automation:
  - alias: Update Xyz left home last
    trigger:
      platform: state
      entity_id: device_tracker.xyz
      from: 'home'
    action:
      - delay: 2
      - service: homeassistant.update_entity
        entity_id: sensor.xyz_left_home_last

The sensor will update when HA starts (which it does anyway), and then whenever the device_tracker leaves home (and every 7 days whether it needs to or not. :wink:)