# Elapsed time with a value

**URL:** <https://community.questdb.com/t/elapsed-time-with-a-value/877>\
**Category:** Community\
**Created:** [July 2, 2025, 6:04pm UTC](https://community.questdb.com/t/elapsed-time-with-a-value/877 "2025-07-02T18:04:10Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![vins](https://avatars.discourse-cdn.com/v4/letter/v/a88e57/32.png) [@vins](https://community.questdb.com/u/vins)\
**Post date:** [July 2, 2025, 6:04pm UTC](https://community.questdb.com/t/elapsed-time-with-a-value/877/1 "2025-07-02T18:04:10Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![javier](https://yyz2.discourse-cdn.com/flex004/user_avatar/community.questdb.com/javier/32/29_2.png) [@javier](https://community.questdb.com/u/javier)\
**Post date:** [July 3, 2025, 4:59pm UTC](https://community.questdb.com/t/elapsed-time-with-a-value/877/2 "2025-07-03T16:59:55Z")

</div>

I think this is what you need

```auto
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