Was wondering if it is possible to know what time a door opens frequently throughout the day or maybe even to know the frequency of when a state is changed to something like ‘on’ for example at a certain time of the day. After researching for a while, haven’t found a way to be able to look into the history of an entity to find when its more frequently opened/changed in a day or even week.
The end goal would be a notification or a custom card on a dashboard saying something like, ‘Throughout the day, the door can be seen frequently opened at around 6pm’
You could use a SQL sensor to get it from the database. For example, the following query gets the hour that my front door is opened the most often:
select hour(from_unixtime(last_updated_ts)) as Hour, count(*) as Count
from states
where entity_id = 'binary_sensor.front_door' and states.state = 'on'
group by hour(from_unixtime(last_updated_ts))
order by 2 desc
limit 1
I’m using MariaDB as the HA database, so if you’re using something else you might have to tweak it a bit.
Currently trying this out and im getting an error in the logs
Logger: homeassistant.components.sql.sensor
Source: components/sql/sensor.py:215
Integration: SQL (documentation, issues)
First occurred: 12:43:26 PM (15 occurrences)
Last logged: 12:50:27 PM
Error executing query select hour(from_unixtime(last_updated_ts)) as Hour, count(*) as Count from states where entity_id = 'binary_sensor.fridge_door' and states.state = 'on' group by hour(from_unixtime(last_updated_ts)) order by 2 desc limit 1 : (sqlite3.OperationalError) no such column: last_updated_ts [SQL: select hour(from_unixtime(last_updated_ts)) as Hour, count(*) as Count from states where entity_id = 'binary_sensor.fridge_door' and states.state = 'on' group by hour(from_unixtime(last_updated_ts)) order by 2 desc limit 1 ] (Background on this error at: https://sqlalche.me/e/14/e3q8)
In my config this is what i have placed:
sql:
- name: Fridge Door opened Frequency
query: >
select hour(from_unixtime(last_updated_ts)) as Hour, count(*) as Count
from states
where entity_id = 'binary_sensor.fridge_door' and states.state = 'on'
group by hour(from_unixtime(last_updated_ts))
order by 2 desc
limit 1
column: "state"
I assume the only difference between yours and mine, is that I’m on MariaDB and I’m guessing you’re on the default database. I wouldn’t have thought they used a different schema. You could install the SQLLite Web add-on and check what columns are available.
However, the “column” should be set to “Hour” - it’s the name of the column containing the state.
If you expand “states > columns”, is this what you see?
It might be worthwhile just doing a “select * from states” on the table, and seeing what date/time columns you have. I’m on HA 2023.3.2, so perhaps there’s been a recent schema change. In my case “last_changed” (as opposed to “_ts”) seems to always be null.
Here is what my columns look like, seems like the attributes is different where yours is longtext and mine is text but last_updated_ts looks the same? Currently I’m on version 2023.2.3, haven’t updated it to march yet.
So “last_updated_ts” is there. I think the problem is you’re missing “db_url” from your sensor. Therefore, it’s possibly accessing your old SQLLite version (homeassistant.db I think it’s called), and that may be an old schema. I used the UI to create the sensor rather than YAML. This means you can change it without having to restart HA.
thats cool, didnt know there was a ui option, was not able to find it under helpers tho? is it only available in the new version after feb? or am i looking at the wrong place?
Wrong place - it’s an integration, so “Settings > Devices & Services > Add Integration > search for SQL”. There’s an “Add Integration” link on the documentation page.
I gave the UI method a try and looks like it works Now I can turn this sensor into a template sensor that would say that the door has been opened frequently at what time, going to try this for a week and see how it goes. I wonder if its possible to make it show as 12 hour with am/pm instead of 24 hour though
EDIT: Initially it looked like the upgrade to 2023.4.1 was leaving “states.entity_id” behind. However, the database upgrade takes quite a while, and it becomes null. Therefore the SQL needs to change to:
select hour(from_unixtime(last_updated_ts)) as Hour, count(*) as Count
from states
where
metadata_id = (select metadata_id from states_meta where states_meta.entity_id = 'binary_sensor.front_door')
and states.state = 'on'
group by hour(from_unixtime(last_updated_ts))
order by 2 desc
limit 1
At the moment I’m getting incorrect results from this in HA, although the query run under myPHPAdmin returns the correct result. I’m hoping this is related to the upgrade too, and will be corrected eventually.
After much angst I have finally been guided into the right place to determine this query is not quite right.
The “from_unixtime” function returns “local” time. When you connect through myphpadmin the session’s timezone is “GLOBAL”, which is probably your local time. However, with SQL Sensor, the session’s timezone is UTC. Therefore you get inconsistent results, and you would consider those from the sensor to be wrong (unless you like UTC).
If you convert the dates from session timezone to global timezone, you get consistent results, and assuming the global timezone was set up to be your local zone, they will be correct.
select hour(CONVERT_TZ(from_unixtime(last_updated_ts), @@session.time_zone, @@global.time_zone)) as Hour, count(*) as count
from states
where
metadata_id = (select metadata_id from states_meta where states_meta.entity_id = 'binary_sensor.front_door')
and states.state = 'on'
group by hour(CONVERT_TZ(from_unixtime(last_updated_ts), @@session.time_zone, @@global.time_zone))
order by count desc;
Been monitoring and seems to be the case too, it would only display the time differently. Thanks for checking on the code from ur side too, would give the new code a try as well and let ya know how it goes after some testing
Edit:
It seems like I’m getting an error from trying to use the code above with the UI since the spacing doesn’t let me keep it on one line
Hm, that’s what I’ve got and I did another copy/paste and it still works. Maybe your copy/paste missed something? The screenshot of mine looks the same, but no error.
Ah I guess the updated SQL only applies after 2023.4.*. You could either upgrade or in the meantime use SQL that will probably break later:
select hour(CONVERT_TZ(from_unixtime(last_updated_ts), @@session.time_zone, @@global.time_zone)) as Hour, count(*) as count
from states
where entity_id = 'binary_sensor.front_door'
and states.state = 'on'
group by hour(CONVERT_TZ(from_unixtime(last_updated_ts), @@session.time_zone, @@global.time_zone))
order by count desc;
I initially raised an issue for this, and it seems that there was a regression in 2023.4 that is being fixed (see here). However given the SQL above is converting from local session timezone to global timezone I think it should work either way. Best keep a check on it though.
Now I can turn this sensor into a template sensor that would say that the door has been opened frequently at what time, going to try this for a week and see how it goes. I wonder if its possible to make it show as 12 hour with am/pm instead of 24 hour though
