Query time spent in state

Setup, Configuration and Use Beginners
#1

Hello,

Looking to generate some statistics from the data I’ve collected around the house. One such item is the total time the patio doors have spent open in a given period. Is there a function built into openHab to easily work this out, or would a MySQL query be best?

Thanks

#2

There isn’t a super simple way but there are ways.

One approach is to save the Contact’s state every minute and do a MyDoor.sumSince(now.minusHours(24))

Because a Contact is stored in the DB as 1==OPEN the result from that simSince will be the number of minutes the door has been OPEN in the past 24 hours.

Another alternative is to have a cron triggered rule that increases a Number Item whenever the rule triggers and the door is OPEN. Just remember to have a rule that triggers at midnight to reset the Number to 0. This basically does the same thing as the above without filling up your DB with a new value every minute.

A third approach is to have a rule that triggers when the door changes from OPEN to CLOSED. In this rule you can use now.millis - MyDoor.perviousState(true).timestamp.time to get the number of milliseconds the door was OPEN. Then just divide as desired to convert that to minutes and update your Items.

These last two approaches work best in cases where you reset your counts every night at a fixed time. If you want to have a running total you will either have to add in a bunch of book keeping or use the once a minute approach in the first option.

#3

Thank you for your reply.

I had a look at the MySQL query route, and by using the DBA Stack Exchange post found here, came up with this monster:

#This part of the query sums the elapsed minutes by whatever grouping criterion is given, and the WHERE clause can be used to select dates.
#Remove the group and order clauses for a simple total
select date(run_start) as start_date, sec_to_time(sum(time_to_sec(elapsed_min))) as time_open
from
	#This part of the query calculates total on time for each OPEN/CLOSE pair and tabulates it
	#e.g. date, run_start (open), run_end (close), elapsed_min
	(select *, Timediff(run_end, run_start) elapsed_min
	from
		#This part of the query matches OPEN times to CLOSE times and tabulates it
		#e.g. date, run_start (open), run_end (close)
		(select t1.Time as run_start  
			, (select min(Time)      
				from item46 t2      
				where t2.Time > t1.Time       
				and t2.Value = 'CLOSED') as run_end
		from item46 t1
		where t1.Value = 'OPEN'
		order by t1.Time) t) u
#Criteria for selection. Remove all criteria for a grand total
where run_start > '2016-07-19 23:59:59'
group by start_date 
order by start_date;

This gives the following output:

`
start_date | time_open

2016-07-20 | 03:26:34
2016-07-21 | 02:36:56
2016-07-22 | 04:57:05
…etc…
`
If you want to make sense of that query, I think you’re best of starting with the two inner most queries and working out. Note that this is carried out on MySQL table item46, which holds the state for our patio doors. It assumes that every open has a close, so if the state of the item is currently open it will ignore it.

I guess the question that naturally leads on from here is how can I get this information into OpenHAB? Is it possible to have a MySQL query defined as an item, or would I have to use it in a rule somehow, where an item’s value is updated with this query whenever the door closes?

Thanks for the help!

Edit

Being able to use this in a rule would be best actually. Given that the query could be used for any occasion you want to know the time an item spent in a certain state e.g. total time lights were on, total time a door was open etc, I would probably look to create a function that wraps this query up, which could then be simply called as

queryTimeSpentInState(item, startCondition(e.g. OPEN), endCondition(e.g. CLOSE), startDate(e.g.2016-07-20), endDate(e.g. 2016-07-27))

#4

I know of no way to execute an SQL query directly from inside a rule. I think the closest you can get is to implement the query in an external script or program and call that program using executeCommandLine. It might be possible to import and set up a separate connection to your DB in the rule but not sure how/if to get the right libraries into the Rule’s classpath so you can access them inside your rules.

NOTE: This query solution would only work with SQL type databases and will not work with rrd4j, db4o, MongoDB, etc.