Calculating total time while certain condition is met Ingres Database

Question

I am having trouble figuring out how to calculate the total time while a certain condition is met. For example,

Date	Speed
25-jan-2021 15:06:06	150.000
25-jan-2021 15:07:06	150.000
25-jan-2021 15:08:07	150.000
25-jan-2021 15:09:18	100.000
25-jan-2021 15:10:18	150.000
25-jan-2021 15:11:19	150.000
25-jan-2021 15:12:20	150.000
25-jan-2021 15:13:20	100.000
25-jan-2021 15:14:20	150.000

Date is type ingresdate and Speed is type float

I need to only calculate the time elapsed while the speed is > 100. Originally I had, Select MAX(Date) - MIN(Date) AS Difference from table WHERE Speed > 100 However, as you can probably already tell, that only returns the time elapsed between the first date and the last date. The answer I need in this example would be about six minutes if my math is correct. Any help at all would be much appreciated. Thank you.

Answer 1

The main idea is to divide the ENTIRE table into segments and sum only the necessary (> 100) segments.

In PostgreSQL this one will work:

    with t as (select '25-jan-2021 15:06:06'::timestamp as Date,
                      '150.000'::float as Speed
               union
               select '25-jan-2021 15:07:06','150.000'
               union
               select '25-jan-2021 15:08:07','150.000'
               union
               select '25-jan-2021 15:09:18','100.000'
               union
               select '25-jan-2021 15:10:18','150.000'
               union
               select '25-jan-2021 15:11:19','150.000'
               union
               select '25-jan-2021 15:12:20','150.000'
               union
               select '25-jan-2021 15:13:20','100.000'
               union
               select '25-jan-2021 15:14:20','150.000'
               )
select
    sum(case when z1.Speed > 100 then (z1.Date - z2.Date)::interval end ) as duration_1,
    sum(case when z1.Speed > 100 then extract(epoch from (z1.Date - z2.Date)) end) as duration_in_sec,
    sum(case when z1.Speed > 100 then extract(epoch from (z1.Date - z2.Date)) end)/60 as duration_in_min_part
from (select t.Date,
             t.Speed,
             row_number() OVER (order by t.Date) as rnum
      from t) z1
inner join (select t.Date,
             t.Speed,
             row_number() OVER (order by t.Date) as rnum
      from t) z2
on z1.rnum + 1 = z2.rnum;

output_result