日期或时间戳的Datediff函数

qgelzfjb 于 2022-09-18 发布在 Java

关注(0)|答案(1)|浏览(179)

我有一个功能，计算2个日期或时间戳之间的差异，这是工作正常。

有没有办法修改该函数以将差值中时间戳的小数部分显示为结果的一部分。如果可能的话，我希望这两个案件都在同一个职能部门处理。

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

oracle

来源：https://stackoverflow.com/questions/73702220/datediff-function-for-dates-or-timestamps

1条答案

按热度按时间

lyfkaqu11#

您希望将TIMESTAMP参数传递给函数，并且在添加年份时还需要确保传播时间戳的小数部分(因为ADD_MONTHS返回没有小数秒的DATE数据类型)：

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

然后：

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

产出：

Diff

41年2天7小时36分56.3277秒

fiddle

赞(0）回复(0）举报 2022-09-18

我来回答

日期或时间戳的Datediff函数

1条答案

相关问题

热门标签

最新问答