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
#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
#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
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!
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))