General
Fan

## How to subtract two 'time' values

Hi All,
I need to frame a query to know the percentage of hours my online screens are up. We have 3 inputs (end_time, SLA, available_hrs). Below is the formula,
case
when end_time > SLA
then 100*(available_hrs - ((end_time - SLA)/a.availhrs))
else 99.99

here both end_time and SLA are in time format (hh:mm:ss.ssssss) and available_hrs is an integer. Can you please help we with a query for this logic.

Accepted Solutions
Junior Contributor

## Re: How to subtract two 'time' values

end_time - SLA can be calculated like this:

```   (Extract(HOUR   From (end_time - SLA HOUR TO SECOND)) +
(Extract(MINUTE From (end_time - SLA HOUR TO SECOND))/60.00) +
(Extract(SECOND From (end_time - SLA HOUR TO SECOND))/3600))```

Plug this into your formula, which can be simplified to

`100 * (1-((end_time - SLA)/available_hrs))`
1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

## Re: How to subtract two 'time' values

I don't get your logic, can you show some example data and exprected result?
Fan

## Re: How to subtract two 'time' values

Below is an example,

Consider my online screens will be up for 12hours (from 7AM - 7PM). The SLA of my batch completion is 7AM. But say the batch has completed at 8AM for today. Due to this my online screens are down for an extra hour and it is up from 8AM to 7AM. Now I need this in a percentage value.

If end_time < SLA then 99.99

If end_time > SLA then  ---> 8AM > 7AM

100* ((available_hrs - (end_time - SLA))/available_hrs)

----> 100* (( 12 - (8AM - 7AM))/12)

----> 100* ((12-(1))/12)

----> 100 * (11/12) ----> 100* (0.916667) -----> 91.67%

Junior Contributor

## Re: How to subtract two 'time' values

end_time - SLA can be calculated like this:

```   (Extract(HOUR   From (end_time - SLA HOUR TO SECOND)) +
(Extract(MINUTE From (end_time - SLA HOUR TO SECOND))/60.00) +
(Extract(SECOND From (end_time - SLA HOUR TO SECOND))/3600))```

Plug this into your formula, which can be simplified to

`100 * (1-((end_time - SLA)/available_hrs))`
Fan

## Re: How to subtract two 'time' values

Thank you so much Dieter!