CODEWITHSUNDEEP
oracle-databaseplsql
user_odoo
user_odoo

Reputation: 2358

How calculate working time in Oracle PLSQL

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

Upvotes: 0

Views: 107

Answers (1)

MT0
MT0

Reputation: 168796

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:

INTERVAL HHMM
-000000000 00:15:00.000000000 -00:15

fiddle

Upvotes: 2

Related Questions