I need to get the history of a device_tracker entity that is not just points on the map. I would love to be able to get the lat/long for a specific time period, also including the speed of the device at the time of the location being recorded.
I know the data must exist because you can show several hours of points on the map, so it’s getting the lat/long from somewhere, I would like to get that data myself, along with any other data that is stored.
In case anyone’s wondering, I struck a deer with my vehicle and would like to have the data as evidence in case the adjuster wants to try and claim speed was a factor, which it wasn’t since I was going right around the speed limit of 65MPH and the damage to the vehicle doesn’t support a “high speed” impact. I don’t need data from point of impact, just the last recorded speed before the incident.
So no way to get the history of a device_tracker out of the database?
Hello!
I too would like to query the history data for device tracker. My kids will have their phones on them with the HA app, which will report their position (quite accurately and without being a drain on the battery). The problem is - I see the instant position, not the history.
The device tracker sensor reports attributes such as:
source_type: gps
latitude: 12.345
longitude: 67.890
gps_accuracy: 14
altitude: 115
course: 0
speed: 0
vertical_accuracy: 6
friendly_name: my android phone
I’m using the recorder component with a mysql database connection, so querying the data should be easy. My end goal is to create a custom Grafana dashboard with MySQL backend where a SQL query can produce timestamps + latitude/longitude for a given device_tracker so I can use the Geomap visualization.
I’m looking through the databse, trying to figure out where the data gets stored, but I need to build some joins… I wonder if the databse schema is documented…
The problem is - looking in the states table, and doing a simple distinct for entity_id (SELECT distinct(entity_id) FROM states; ), I get only NULL…
I’ll start reading on https://data.home-assistant.io/, and see where it takes me…
Thanks to the docs above I managed to put together an SQL query that shows me the history for a device_tracker, from the database. Note it’s in MySQL format, so if you’re using sqlite, you may need to adjust the query:
SELECT states_meta.entity_id, state_attributes.shared_attrs, from_unixtime(states.last_updated_ts) FROM states LEFT JOIN states_meta ON (states.metadata_id=states_meta.metadata_id) LEFT JOIN state_attributes ON (states.attributes_id=state_attributes.attributes_id) WHERE last_changed_ts is NULL and states_meta.entity_id = 'device_tracker.my_android_phone';
Now, I need to figure out how to split the json data and select specific fields without resorting to regex…
That’s a useful query, thank you for sharing.