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!