# How to calculate uptime rate comparing timestamps by status

I have a table where I record http calls. Here’s how my table looks like:

callTimestamp httpStatus endpoint
2021-04-01 06:00:00 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:21:11 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:25:00 500 https://someserver/someapi/v1/endpoint1
2021-04-01 11:33:15 200 https://someserver/someapi/v1/endpoint1
2021-04-01 11:34:31 200 https://someserver/someapi/v1/endpoint1
2021-04-01 11:35:22 500 https://someserver/someapi/v1/endpoint1
2021-04-01 12:22:54 200 https://someserver/someapi/v1/endpoint1
2021-04-01 10:21:11 200 https://someserver/someapi/v1/endpoint2
2021-04-01 10:25:32 500 https://someserver/someapi/v1/endpoint2
2021-04-01 10:59:12 200 https://someserver/someapi/v1/endpoint2

I need to calculate the uptime rate, which is:

1. The total uptime rate (100%) in seconds is TIME_TO_SEC(TIMEDIFF(NOW(), MIN(callTimestamp)))

2. Need to calculate the (downtime) difference between the record with httpStatus 500 and it’s subsequent with httpStatus 200 grouped by endpoint. From the example above for endpoint1, I have two ocurrences:

2021-04-01 11:33:15 – 2021-04-01 10:25:00 = 4095 seconds

2021-04-01 12:22:54 – 2021-04-01 11:35:22 = 2852 seconds

Example of expected result
Considering
MIN(callTimestamp) = 2021-04-01 06:00:00
NOW() = 2021-04-01 22:00:00

TIME_TO_SEC(TIMEDIFF(NOW(), MIN(callTimestamp))) = 57600 seconds (this is my 100%)

endpoint1 = 57600 – 6947 = 50563 seconds uptime
endpoint2 = 57600 – 2020 = 55580 seconds uptime

uptimeRate endpoint
87.93 https://someserver/someapi/v1/endpoint1
96.49 https://someserver/someapi/v1/endpoint2

I don’t have idea of how to calc the downtime by endpoint.

Any ideas of how could I do this?

P.S: I’m using MySQL 8.0.20

You can use `lead()over()` to calculate next `callTimestamp` and `min()over()` window function to select minimum `callTimestamp`. Then with group by and aggregation you can get what you are looking for.

Schema and insert statements:

``` create table mytable(callTimestamp datetime,   httpStatus int, endpoint varchar(100));
insert into mytable values('2021-04-01 10:21:11',200,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 10:25:00',500,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:33:15',200,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:34:31',200,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 11:35:22',500,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 12:22:54',200,  'https://someserver/someapi/v1/endpoint1');
insert into mytable values('2021-04-01 10:21:11',200,  'https://someserver/someapi/v1/endpoint2');
insert into mytable values('2021-04-01 10:25:32',500,  'https://someserver/someapi/v1/endpoint2');
insert into mytable values('2021-04-01 10:59:12',200,  'https://someserver/someapi/v1/endpoint2');
```

Query:

``` select endpoint, 100*(TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime)))-sum(TIME_TO_SEC(TIMEDIFF(nexttime, calltimestamp))))/ TIME_TO_SEC(TIMEDIFF(NOW(), max(mincalltime))) uptimeRate

from
(select *,lead(calltimestamp)over (partition by endpoint order by calltimestamp)nexttime,
min(calltimestamp)over(partition by endpoint order by calltimestamp) mincalltime
from mytable
)t
where httpstatus=500
group by endpoint
```

Output:

endpoint uptimeRate
https://someserver/someapi/v1/endpoint1 98.8895
https://someserver/someapi/v1/endpoint2 99.6771

db<fiddle here