How long has the machine run?

from the Artful Common Queries page

We have a table that tracks a machine's on and off times. We wish to know its total on time. 
drop table t;
create table t( 
  mach char(3),
  loc char(4),
  ts timestamp,
  status tinyint
);
insert into t values
('m42', 'Chus', '2024-12-17 23:18:56', 1 ),
('m42', 'Chus', '2024-12-17 23:20:44', 0 ),
('m42', 'Chus', '2024-12-17 23:21:51', 1 ),
('m42', 'Chus', '2024-12-17 23:24:51', 0 ),
('m42', 'Chus', '2024-12-17 23:25:03', 1 ), 
('m42', 'Chus', '2024-12-17 23:26:40', 0 ), 
('m42', 'Chus', '2024-12-17 23:28:58', 1 ), 
('m42', 'Chus', '2024-12-17 23:29:04', 0 ), 
('m42', 'Chus', '2024-12-17 23:31:05', 1 ), 
('m42', 'Chus', '2024-12-17 23:31:17', 0 ), 
('m42', 'Chus', '2024-12-17 23:31:41', 1 ), 
('m42', 'Chus', '2024-12-17 23:34:59', 0 ), 
('m42', 'Chus', '2024-12-17 23:36:24', 1 ), 
('m42', 'Chus', '2024-12-17 23:46:02', 0 ), 
('m42', 'Chus', '2024-12-17 23:46:14', 1 ), 
('m42', 'Chus', '2024-12-17 23:46:20', 0 ), 
('m42', 'Chus', '2024-12-17 23:51:39', 1 ), 
('m42', 'Chus', '2024-12-17 23:56:16', 0 ), 
('m42', 'Chus', '2024-12-17 23:56:34', 1 );
To add and subtract times, convert them to seconds since 1970 with the unix_timestamp() function.

Then what? Originally, SQL didn't have tools for row-to-row computation. ISO SQL 2003 added 'windowing functions' for doing this. It was a decade and a half before MySQL and MariaDB implemented them, but since MySQL 8 and MariaDB 10.2 we have them, so if you're running one of those, the solution is to use the Lead() windowing function to fetch the next row after each machine turn-on, then sum those times.

Computational queries like this are usually easiest to build from the inside out. The inside query defines the dataset: 

select unix_timestamp(ts) as ts, status
from t
order by ts;
From that dataset, fetch row-to-row time diffs ... 
select status, lead(ts,1,ts) over w - ts as 'timediff'
from (
  select unix_timestamp(ts) as ts, status
  from t
  order by ts
) x
window w as (order by ts);
+--------+----------+
| status | timediff |
+--------+----------+
|      1 |      108 |
|      0 |       67 |
|      1 |      180 |
|      0 |       12 |
|      1 |       97 |
|      0 |      138 |
|      1 |        6 |
|      0 |      121 |
|      1 |       12 |
|      0 |       24 |
|      1 |      198 |
|      0 |       85 |
|      1 |      578 |
|      0 |       12 |
|      1 |        6 |
|      0 |      319 |
|      1 |      277 |
|      0 |       18 |
|      1 |        0 |
+--------+----------+
We're interested only in the rows with status=1. Their `timediff` values are the on times we want. So filter out other rows, sum the remaining rows, and convert the result back to hrs:secs:mins ... 
select sec_to_time( sum(ontime) ) as 'on time'
from (
  select 
    status, 
    lead(ts,1,ts) over w - ts as 'ontime'
  from (
    select 
      unix_timestamp(ts) as ts, 
      status
    from t
    order by ts
  ) x
  window w as (order by ts)
) y
where status=1;
+----------+
| on time  |
+----------+
| 00:24:22 |
+----------+
If you've not yet moved to MySQL 8 and MariaDB 10.2, you don' have functions like Lead(). The traditional MySQL solution for such problems was to compute row-to-row with user variables, but that's deprecated, not guaranteed to produce consistent results. What to do?

Take advantage of the arithmetic fact that the sum of the differences = the difference of the sums: if we filter the dataset so it consists only of row pairs where the first row has status=1 and the second has status=0, we have time value pairs where the first time is turn-on time and the second is turn-off time. Our answer is the difference between those two sums. So the dataset is ... 

  select unix_timestamp(ts) as ts, status
  from t
  where ts >= ( select min(ts) from t where status=1 )
    and ts <= ( select max(ts) from t where status=0 )
  order by ts;
... then subtract the summed off times from summed on times and convert the result to hrs:mins:secs ... 
select sec_to_time( sum(if(status=0,ts,0)) - sum(if(status=1,ts,0)) ) as 'on time' 
from (
  select unix_timestamp(ts) as ts, status
  from t
  where ts >= ( select min(ts) from t where status=1 )
    and ts <= ( select max(ts) from t where status=0 )
) x;
+----------+
| on time  |
+----------+
| 00:24:22 |
+----------+

Last updated 22 Feb 2025