Finding the most frequent time a door is opened based on a history of an entity

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’

EDIT: See further down here for the correct SQL.

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.

2 Likes

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.

Im also using MariaDB with my host, tried using phpMyAdmin and this is what i get too

If you expand “states > columns”, is this what you see?

image

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.

Heres what a “select * from states” looks like on the table

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.

1 Like

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?

Heres what my helpers look like,

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.

1 Like

I gave the UI method a try and looks like it works :slight_smile: 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

image

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;
1 Like

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 :slight_smile:

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
image

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.

image

hm thats weird, heres what the last 2 lines looks like, tried copy pasting it a few times but still got SQL Query invalid, the formating seems correct

image

I tried running the same SQL query in phpMyAdmin and I also got an error here saying #1146 - Table 'homeassistant.states_meta' doesn't exist

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;
1 Like

seems like it only works after 2023.4 as u said, works now after the update :slight_smile:

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.

1 Like

After a few days, I noticed it would stay stuck at a certain time and not update to the latest time the door has been opened on frequently in the day.