Elapsed time with a value

Community
1

i have a table (services) that contains the status (0=DOWN, 1=UP) for a service. it’s something like this:

timestamp service status
2025-07-02T17:29:40.268000Z ldap 1
2025-07-02T17:29:40.309000Z radius 1
2025-07-02T17:29:50.327000Z ldap 0
2025-07-02T17:29:50.559000Z radius 1
2025-07-02T17:30:00.293000Z ldap 0
2025-07-02T17:30:00.349000Z radius 0
2025-07-02T17:30:10.296000Z ldap 1
2025-07-02T17:30:10.439000Z radius 1

i was wondering how can i get number the exact number of seconds each service it’s been up (1).
for example, if current timestamp is 17:30:10, previous table should return 10 seconds for ldap and 20 seconds for radius.
i’ve been playing around with window functions, but i can’t manage to figure it out. any clue?
cheers!

2

I think this is what you need

with status_and_next as (
select timestamp, service, status, 
lead(status) over (partition by service order by timestamp) as next_status,
lead(timestamp) over (partition by service order by timestamp) as next_ts
from service_status
),
elapsed as (
  select *, case when status = 1 then datediff('u',timestamp, next_ts) end as time_up from status_and_next
)
select service, sum(time_up) as total_time
from elapsed;

Basically for every row where status is 1, calculate the time difference in microseconds with the next timestamp for same symbol and name it time_up in an intermediate query. For rows with status 0, null for time_up.

Then you just have to sum all the time_up grouped by service