Uptime/Downtime Query

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?

Thanks for your help.

1 Like

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

1 Like

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.

1 Like

Thanks to both of you. I think I’ve got something that will work. It took way more time than I wanted it to, but I learned a lot!