MySQL calculate ON-OFF time from datetime

I got a basic one for the duration except for the last bit from 'ON' to 'NOW()'. It looks like this:

SELECT SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime))) AS duration
FROM Stack AS S1,
     Stack AS S2
WHERE S1.id + 1 = S2.id AND
      S1.status = 'ON'

This can probably be written as a JOIN as well, most people prefer that, but that's equivalent to this. There's a bit of juggling with the times, as you can see. The result is:

06:30:00

I spotted an error in your database, there are two id's with the value 7. I changed the second one to 8 and now the result is:

04:30:00

That cannot be right. Haha... sorry, let me think. OK, checked it, and it is correct. The 06:30:00 was caused by the error in the database.

The next query will compute the remaining time at the end of the database table:

SELECT SEC_TO_TIME(TIMESTAMPDIFF(SECOND, `datetime`, NOW())) AS `duration`
FROM `Stack` 
WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
      `status` = 'ON'

It now return the ridiculous:

838:59:59

This is not the right result, so ignore it. The date is simply too far back in the past for this query to work. To get a reasonable result from the database I changed all the dates from 2020-01-01 to 2021-01-22.

Finally we need to combine these two. And that's relatively simple:

SELECT
  SEC_TO_TIME(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, S1.datetime, S2.datetime)) AS duration
     FROM Stack AS S1,
          Stack AS S2
     WHERE S1.id + 1 = S2.id AND
           S1.status = 'ON')
     +
     (SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
      FROM `Stack` 
      WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
            `status` = 'ON')
   );

And that should do it. Now I am sure there must be a better way to do this, but hey, it works!

Oh, if the last status is 'OFF' it results NULL. Let me work on that. This should do something:

SELECT
  SEC_TO_TIME(CAST(
    (SELECT SUM(TIMESTAMPDIFF(SECOND, `S1`.`datetime`, `S2`.`datetime`)) AS duration
     FROM Stack AS `S1`,
          Stack AS `S2`
     WHERE `S1`.`id` + 1 = `S2`.`id` AND
           `S1`.`status` LIKE 'ON')
     +
     IFNULL((SELECT TIMESTAMPDIFF(SECOND, `datetime`, NOW()) AS `duration`
             FROM `Stack` 
             WHERE `id` = (SELECT MAX(`id`) FROM `Stack`) AND
                   `status` LIKE 'ON'), 0)
   AS UNSIGNED));

I added the CAST(.... AS UNSIGNED) to remove the anything after the second.

You say this is about one device. This is the table you have:

+----+---------------------+--------+
| ID |  DATETIME           | STATUS |
+----+---------------------+--------+
|  1 | 2020-01-01 10:00:00 | ON     |
|  2 | 2020-01-01 11:00:00 | ON     |
|  3 | 2020-01-01 12:00:00 | OFF    |
|  4 | 2020-01-01 12:30:00 | OFF    |
|  5 | 2020-01-01 15:00:00 | ON     |
|  6 | 2020-01-01 16:00:00 | ON     |
|  7 | 2020-01-01 17:30:00 | OFF    |
|  8 | 2020-01-01 18:00:00 | ON     |
+----+---------------------+--------+

These are my conclusions:

1. We see the device is on at 10:00. Was it on before? We don't know. Hence we consider this the first switch-on time.
2. Why are we getting this? Has the device failed to send an OFF in between? Well, let's consider this "still on".
3. Switched off.
4. Why are we getting this? Has the device failed to send an ON in between? Well, let's consider this "still off".
5. Switched on.
6. Why are we getting this? Has the device failed to send an OFF in between? Well, let's consider this "still on".
7. Switched off.
8. Switched on.

This means we must always find the next status change. ON directly after ON gets ignored. Same for OFF after OFF.

Steps:

1. Add a row with the current time and OFF for easier calculation later.
2. Look into the previous row. Only keep rows that have a different status from the previous row.
3. For easier calculation, I convert the timestamps to seconds since a fixed date in the part.
4. Now it's mere aggregation and getting back from seconds only to hours, minutes, and seconds.

The query:

select
  sec_to_time(sum(case when 'OFF' then secs else -secs end))
from
(
  select
    timestampdiff(second, date '1990-01-01', datetime) as secs,
    status, 
    lag(status) over (order by datetime) as prev_status
  from 
  (
    select datetime, status from Sack
    union all
    select now(), 'OFF'
  ) end_row_added
) with_previous_status
where status <> prev_status or prev_status is null;

You need MySQL 8 for this. Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9a3e500bafb09a9b3b5295ac715b6236