Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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)

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading