difference between 2 dates in days, hours, minutes and seconds-CodePudding

I have a function below, which shows the difference between 2 dates in days, hours, minutes and seconds, which is working fine.

I want to expand the function to include the number of years but I'm unsure how to DECLARE the variable in the function. I know it has to be larger than day(3) to second(0). Is there something like a year(3) to second(0)?

As you can see I get the following error when the DATE spans years. Any help would be appreciated.


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE  OR REPLACE FUNCTION datediff (p_from date, p_to date)
return varchar2 is
  l_interval interval day(3) to second(0);
begin
  l_interval := cast(p_to as timestamp) - cast(p_from as timestamp);
  return 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('2022-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
2 Days 7 Hours 36 Minutes 56 Seconds


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;

ORA-01873: the leading precision of the interval is too small

CodePudding user response：

Is there something like a year(3) to second(0)?

It does not make sense to have a generic INTERVAL data type that contains years, days, hours, minutes and seconds.

If you have an interval of 365 days then if your interval starts on 2022-01-01 than that equals 1 year but if the interval starts on 2020-01-01 then is less than one year as there are 366 days in 2020 due to it being a leap year. Therefore, the number of days in a year depends on which year you are talking about and you cannot have a generic interval that combines days with either months or years as it does not make sense as you need a specific start date.

If the date spans years then you could edit your function and increase the leading precision of the INTERVAL to interval day(5) to second(0) which will let you store about 273 years and then your function will work for your sample data.

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

Outputs:

DIFF

14977 Days 7 Hours 36 Minutes 56 Seconds

DIFF
14977 Days 7 Hours 36 Minutes 56 Seconds

However, since you have known start- and end-dates then you can find the number of full years between the two bounds and then use an INTERVAL DAY TO SECOND data type calculate the number of days, hours, minutes and seconds on the part-year remainder:

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;
/

Then:

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

Outputs:

DIFF

0 Years 2 Days 7 Hours 36 Minutes 56 Seconds

DIFF
0 Years 2 Days 7 Hours 36 Minutes 56 Seconds

and:

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

Outputs:

DIFF

41 Years 2 Days 7 Hours 36 Minutes 56 Seconds

DIFF
41 Years 2 Days 7 Hours 36 Minutes 56 Seconds

db<>fiddle here