How calculate working time eg.

7.5 = 7h and 30 min (working hours)

0.75 = 45 min (pause)

8 = 8h (Planing hours)

How get result eg. (-15 min) below query return 00:15 is it possible get in minus or use have better example?

Select
      to_char(time'0:0:0' numtodsinterval((7.5   0.75 - 8 ),'hour'),'hh24:mi')
from dual

CodePudding user response：

You have the arithmetic backwards and to get a negative number you want 8 - (7.5 0.75).

Don't use a time and just use the interval (and extract the sign, hour and minute components using string functions if you want a different format):

SELECT numtodsinterval(8 - (7.5   0.75),'hour') AS interval,
       REGEXP_REPLACE(
         numtodsinterval(8 - (7.5   0.75),'hour'),
         '([ -]?)(\d ) (\d ):(\d ):(\d \.?\d*)',
         '\1\3:\4'
       ) AS hhmm
FROM   DUAL;

Outputs:

fiddle