How to subtract two 'time' values

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.

 

Thanks in advance


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!