I have a function that calculates the difference between 2 dates or timestamps which is working fine.

Is there a way to modify the function to display the fractional part of the TIMESTAMP in the difference as part of the result. I want both cases to be handled in the same function if possible.

CREATE  OR REPLACE FUNCTION datediff (p_from date, p_to date)
return varchar2 is
  l_years    PLS_INTEGER;
  l_from     DATE;
  l_interval interval day(3) to second(0);
begin
  l_years := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from := ADD_MONTHS(p_from, l_years * 12);
  l_interval := (p_to - l_from) DAY(3) TO SECOND(0);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/

SELECT 
datediff( TO_DATE('1981-04-01 10:11:13','YYYY-MM-DD HH24:MI:SS'), 
TO_DATE('2022-04-03 17:48:09','YYYY-MM-DD HH24:MI:SS')) as  diff FROM DUAL;

DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds


SELECT 
datediff (TO_TIMESTAMP('1981-04-01 10:11:13.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'),
TO_TIMESTAMP('2022-04-03 17:48:09.878700000', 'YYYY-MM-DD HH24:MI:SS.FF')) as  diff FROM DUAL;

/* want to show fractional difference here */

DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds 

CodePudding user response：

You want to pass TIMESTAMP arguments to the function and when you add years you also need to ensure that you propagate the fractional component of the timestamp (since ADD_MONTHS returns a DATE data-type without fractional seconds):

CREATE  OR REPLACE FUNCTION datediff (p_from timestamp, p_to timestamp)
return varchar2 is
  l_years    PLS_INTEGER;
  l_from     TIMESTAMP;
  l_interval interval day(3) to second(6);
begin
  l_years    := TRUNC(MONTHS_BETWEEN(p_to, p_from)/12);
  l_from     := CAST(TRUNC(ADD_MONTHS(p_from, l_years * 12), 'MI') AS TIMESTAMP)
                    NUMTODSINTERVAL( EXTRACT(SECOND FROM p_from), 'SECOND' );
  l_interval := (p_to - l_from) DAY(3) TO SECOND(6);
  return l_years || ' Years '
    || extract (day from l_interval) || ' Days '
    || extract (hour from l_interval) || ' Hours '
    || extract (minute from l_interval) || ' Minutes '
    || extract (second from l_interval) || ' Seconds';
end datediff;
/

Then:

SELECT datediff(
         TIMESTAMP '1981-04-01 10:11:13.551000000',
         TIMESTAMP '2022-04-03 17:48:09.878700000'
       ) as  diff
FROM   DUAL;

Outputs:

fiddle