Time Function used to calculate total Time between two time given in parameter

 The following function will calculate total seconds getting parameter of two time values:

CREATE OR REPLACE FUNCTION F_TIME (VTIMEIN VARCHAR2,VTIMEOUT VARCHAR2) RETURN number IS

  VSEC_IN        NUMBER(10);

  VSEC_OUT       NUMBER(10);

  VHH            VARCHAR2(8);

  VMM            VARCHAR2(8);

  VSS            VARCHAR2(8);

  V_TOT_WORKED   NUMBER(10);

BEGIN

  VSEC_IN :=TO_NUMBER(SUBSTR(VTIMEIN,1,2))*60*60+TO_NUMBER(SUBSTR(VTIMEIN,4,2))*60+TO_NUMBER(SUBSTR(VTIMEIN,7,2));

  VSEC_OUT:=TO_NUMBER(SUBSTR(VTIMEOUT,1,2))*60*60+TO_NUMBER(SUBSTR(VTIMEOUT,4,2))*60+TO_NUMBER(SUBSTR(VTIMEOUT,7,2));

  V_TOT_WORKED:=VSEC_OUT-VSEC_IN;

  RETURN V_TOT_WORKED;

END;

/

 

 

The following function will calculated time duration output in HH:MI:SS format:

 

CREATE OR REPLACE FUNCTION F_TIMEWORKED
   (VTIMEIN VARCHAR2,VTIMEOUT VARCHAR2)
   RETURN VARCHAR2 IS
   VSEC_IN                 NUMBER(10);
   VSEC_OUT                NUMBER(10);
   VHH                     VARCHAR2(8);
   VMM                     VARCHAR2(8);
   VSS                     VARCHAR2(8);
   V_TOT_WORKED    NUMBER(10);
   V_WORKED                VARCHAR2(8);
BEGIN
   VSEC_IN :=TO_NUMBER(SUBSTR(VTIMEIN,1,2))*60*60+TO_NUMBER(SUBSTR(VTIMEIN,4,2))*60+TO_NUMBER(SUBSTR(VTIMEIN,7,2));
   VSEC_OUT:=TO_NUMBER(SUBSTR(VTIMEOUT,1,2))*60*60+TO_NUMBER(SUBSTR(VTIMEOUT,4,2))*60+TO_NUMBER(SUBSTR(VTIMEOUT,7,2));
   V_TOT_WORKED:=VSEC_OUT-VSEC_IN;
   VHH:=TRUNC(V_TOT_WORKED/(60*60));
   VMM:=TRUNC((V_TOT_WORKED-VHH*60*60)/60);
   VSS:=TRUNC(V_TOT_WORKED-((VHH*60*60)+VMM*60));
   V_WORKED:=LPAD(VHH,2,'0')||':'||LPAD(VMM,2,'0')||':'||LPAD(VSS,2,'0');
   RETURN V_WORKED;
END;
/

 

SELECT F_TIMEWORKED('10:00:00','18:05:00') FROM DUAL;