I have a boolean tag that is logged every time it changes state. When it’s a 1, the process is active. When it’s a 0, the process is in active. What I’d like to do is query the database to calculate the total active time for a given time period. So essentially I need to grab the elapsed time between each on/off cycle and sum them all together.
Can anyone help me to develop a query that would work for this function? Or should I be approaching this problem in some other way?
without seeing a sample, I can’t be entirely accurate, but it would be something like:
SELECT t1.tagValue,
t1.t_stamp,
SUM(IFNULL(TIME_TO_SEC(timeDIFF((SELECT MIN(t2.t_stamp) FROM tablename t2
WHERE t2.t_stamp > t1.t_stamp
and t2.t_stamp > '2015-04-22 08:00:00'),
t_stamp))
,0))
FROM tablename t1
where t1.t_stamp > '2015-04-22 08:00:00'
group by tagValue
If you are using a database that supports analytic functions (aka window aggregate functions), you can use something like this (tstamp1 and tstamp2 are your bounds):
SELECT sum(least(endts, tstamp2)-greatest(begints, tstamp1)) AS uptime
FROM (
SELECT t_stamp as begints, lead(t_stamp) OVER (ORDER BY t_stamp) as endts, tagvalue
FROM table
ORDER BY t_stamp) AS t2
WHERE endts >= tstamp1 AND begints < tstamp2 AND t2.tagvalue=1;
I believe MySQL doesn’t have this, but all the other major DBs do. The above is the syntax I use for such things in PostgreSQL.
If your log file is very busy, you should add a where clause to the inner select that will pre-filter your timestamp column to give the DB something to optimize. The outer math will work as long as the inner filter includes the events just before and just after your time span.