Calculating elapsed time in non-contiguous rows using SQL

Question

I need to deduce uptime for servers using SQL with a table that looks as follows:

| Row |  ID | Status |  Timestamp  |
-----------------------------------
|  1  |  A1 |   UP   |  1598451078 |
-----------------------------------
|  2  |  A2 |   UP   |  1598457488 |
-----------------------------------
|  3  |  A3 |   UP   |  1598457489 |
-----------------------------------
|  4  |  A1 |  DOWN  |  1598458076 |
-----------------------------------
|  5  |  A3 |  DOWN  |  1598461096 |
-----------------------------------
|  6  |  A1 |   UP   |  1598466510 |
-----------------------------------

In this example, A1 went down on Wed, 26 Aug 2020 16:07:56 and came back up at Wed, 26 Aug 2020 18:28:30. This means I need to find the difference between rows 6 and 4 using the ID field and display it as an additional column named "Uptime".

I have found several answers that explain how to use aliases and inner joins to calculate the difference between contiguous rows (e.g. How to get difference between two rows for a column field?), but none that explains how to do so for non-contiguous rows.

For example, this piece of code from https://www.mysqltutorial.org/mysql-tips/mysql-compare-calculate-difference-successive-rows/ gives a possible solution, but I don't know how to adapt it to compare the roaws based on the ID field:

    SELECT 
    g1.item_no,
    g1.counted_date from_date,
    g2.counted_date to_date,
    (g2.qty - g1.qty) AS receipt_qty
FROM
    inventory g1
        INNER JOIN
    inventory g2 ON g2.id = g1.id + 1
WHERE
    g1.item_no = 'A';

Any help would be much appreciated.

Answer 1

Basically, you need the total time minus the downtime.

If you want the different periods, you can use:

select status, max(timestamp), min(timestamp),
       max(timestamp) - min(timestamp)
from (select t.*,
             row_number() over (order by timestamp) as seqnum,
             row_number() over (partition by status order by timestamp) as seqnum2
      from t
     ) t
group by status, (seqnum - seqnum2);

However, for your purposes, for the total uptime:

select sum( coalesce(next_timestamp, max_uptimestamp) - min(timestamp))
from (select t.*,
             lag(timestamp) over (order by status) as prev_status,
             lead(timestamp) over (order by timestamp) as next_timestamp,
             max(case when status = 'UP' then timestamp end) over () as max_uptimestamp
      from t
     ) t
where status = 'UP' and
      (prev_status = 'DOWN' or pre_status is null);

Basically, this counts all the time from the first UP to the next DOWN or to the last UP. It then sums that up.