Time difference between rows

In a mysql database (ver.5.7.15) I’ve got the following table named operation_list containing the following data: id (autoincrement integer, primary key), operation_date_time (datetime), operation (enumerated values: START,STOP), so the table looks like that:

+------+---------------------+-----------+
| id   | operation_date_time | operation |
+------+---------------------+-----------+
|    1 | 2000-01-01 06:30:45 | START     |
|    2 | 2000-01-01 07:45:00 | STOP      |
|    3 | 2000-01-01 08:18:12 | START     |
|    4 | 2000-01-01 11:23:58 | STOP      |
|    5 | 2000-01-01 15:45:01 | START     |
|    6 | 2000-01-01 19:01:33 | STOP      |
+------+---------------------+-----------+

Now, assuming that the first row is always a START, the last ROW is always a STOP, the STOP is always placed after a START, I need to retrieve the time difference between START and STOP in seconds. Hence, I need to write an SQL that would produce the following recordset:

+------+---------------------+-----------+
| id   | operation_date_time | duration  |
+------+---------------------+-----------+
|    1 | 2000-01-01 06:30:45 | 4455      |
|    3 | 2000-01-01 08:78:12 | 11146     |
|    5 | 2000-01-01 15:45:01 | 11792     |  
+------+---------------------+-----------+

Where 4455 is equivalent to 1 hour, 14 minutes and 15 seconds, 11146 is equivalent to 3 hours, 5 minutes and 46 seconds, 11792 is equivalent to 3 hours, 16 minutes and 32 seconds, and so on.

What’s the best way to do it in a single SQL statement without creating additional tables or dedicated scripting?

Answer

This works IN mysql 5.X

But it is uglier as in 8.0

SELECT
   MIN(id) id
  ,MIN(`operation_date_time`) `operation_date_time`
  ,MAX(diff) duration  
FROM
(SELECT 
  id
  , IF(`operation` = 'START', 0,TIME_TO_SEC(TIMEDIFF(`operation_date_time`,  @datetime))) diff
  ,IF(`operation` = 'START',  @count := @count + 1,@count := @count) groupby
  ,@datetime := `operation_date_time` `operation_date_time`
 FROM
  (SELECT * FROM timetable ORDER by `operation_date_time` ASC) t1, (SELECT @datetime := NOW()) a,
   (SELECT @count := 0) b) t2
GROUP by groupby;

CREATE TABLE timetable (
  `id` INTEGER,
  `operation_date_time` VARCHAR(19),
  `operation` VARCHAR(5)
);

INSERT INTO timetable
  (`id`, `operation_date_time`, `operation`)
VALUES
  ('1', '2000-01-01 06:30:45', 'START'),
  ('2', '2000-01-01 07:45:00', 'STOP'),
  ('3', '2000-01-01 08:18:12', 'START'),
  ('4', '2000-01-01 11:23:58', 'STOP'),
  ('5', '2000-01-01 15:45:01', 'START'),
  ('6', '2000-01-01 19:01:33', 'STOP');

✓

✓

SELECT
   MIN(id) id
  ,MIN(`operation_date_time`) `operation_date_time`
  ,MAX(diff) duration  
FROM
(SELECT 
  id
  , IF(`operation` = 'START', 0,TIME_TO_SEC(TIMEDIFF(`operation_date_time`,  @datetime))) diff
  ,IF(`operation` = 'START',  @count := @count + 1,@count := @count) groupby
  ,@datetime := `operation_date_time` `operation_date_time`
 FROM
  (SELECT * FROM timetable ORDER by `operation_date_time` ASC) t1, (SELECT @datetime := NOW()) a,
   (SELECT @count := 0) b) t2
GROUP by groupby;

id | operation_date_time | duration
-: | :------------------ | -------:
 1 | 2000-01-01 06:30:45 |     4455
 3 | 2000-01-01 08:18:12 |    11146
 5 | 2000-01-01 15:45:01 |    11792

db<>fiddle here