Hi,
I just started out with HA on RPi3.
I use Owntracks which tracks my location using MQTT.
In HA, I defined 4 zones : home + 3 work locations. The tracker works so good, I would like to use it to calculate my average travel time between those locations. So basically : what is the length of the ‘not home’ state inbetween any two (different!) defined zones.
So far I’ve come up with following query to couple the states to the events:
SELECT events.time_fired, old_states.state AS oldState, new_states.state AS newState FROM events
LEFT JOIN states AS new_states ON events.event_id = new_states.event_id
LEFT JOIN states AS old_states ON new_states.old_state_id = old_states.state_id
WHERE events.event_type = 'state_changed'
AND (new_states.domain='person' OR old_states.domain='person')
AND (new_states.last_changed=new_states.last_updated OR old_states.last_changed=old_states.last_updated)
AND oldState is not null
AND not newState=oldState
ORDER BY events.time_fired
This way I get following result:
time_fired oldState newState
-------------------------- ---------- ------------
2020-11-01 16:05:27.759770 home not_home
2020-11-01 16:51:44.962990 not_home home
2020-11-04 07:08:33.244871 home not_home
2020-11-04 07:10:06.239082 not_home home
2020-11-04 07:10:06.459571 home not_home
2020-11-04 07:32:52.138773 not_home Work_1
2020-11-04 16:58:50.466368 Work_1 not_home
2020-11-04 17:20:38.522205 not_home home
2020-11-04 17:20:55.660488 home not_home
2020-11-04 17:21:25.277683 not_home home
2020-11-05 06:12:41.259408 home not_home
2020-11-05 06:12:51.027391 not_home home
2020-11-05 06:13:00.199936 home not_home
2020-11-05 06:22:15.336496 not_home home
2020-11-05 06:58:52.649057 home not_home
2020-11-05 07:19:42.138407 not_home Work_1
2020-11-05 13:54:59.252817 Work_1 not_home
2020-11-05 14:11:02.926787 not_home Work_2
2020-11-05 14:11:10.135953 Work_2 not_home
2020-11-05 14:11:30.227316 not_home Work_2
2020-11-05 16:20:39.177569 Work_2 home
2020-11-06 06:27:21.237012 home not_home
2020-11-06 06:27:31.225074 not_home home
2020-11-06 06:27:41.421047 home not_home
2020-11-06 06:27:54.104123 not_home home
2020-11-06 06:30:21.647734 home not_home
2020-11-06 06:51:57.477086 not_home Work_1
2020-11-06 15:23:33.216417 Work_1 not_home
2020-11-06 15:48:12.536643 not_home home
2020-11-06 15:48:17.517409 home not_home
2020-11-06 15:48:27.520378 not_home home
2020-11-06 15:48:57.498153 home not_home
2020-11-06 15:49:07.234197 not_home home
2020-11-06 16:10:52.063085 home not_home
2020-11-06 16:23:42.041713 not_home home
2020-11-06 16:23:42.327856 home not_home
2020-11-06 16:32:53.230073 not_home home
So the data is there, but I’m struggling to calculate the time differences. Do I need a python script to do this?
Also, I want to dismiss entries where I return to the same zone,
e.g.:
home - not home
not home - home