oracle将日期差转换为时分秒格式

要求：
计算2021/03/03 16:28:00 - 2021/03/03 14:25:01 = 02:02:59
如果时间差小于一天，可以用以下简写方法

with tab1 as (
select to_date('2021/03/03 16:28:00', 'yyyy/mm/dd hh24:mi:ss') -
       to_date('2021/03/03 14:25:01', 'yyyy/mm/dd hh24:mi:ss') tim
  from dual
)
select to_char(trunc(sysdate) + t1.tim, 'hh24:mi:ss')
  from tab1 t1
;

如果时间差大于一天，并且要求多出的天加到小时上
例如2021/03/03 16:28:00 - 2021/03/02 14:25:01 = 26:02:59

with tab1 as (
select to_date('2021/03/03 16:28:00', 'yyyy/mm/dd hh24:mi:ss') -
       to_date('2021/03/02 14:25:01', 'yyyy/mm/dd hh24:mi:ss') tim
  from dual
)
select trunc(t1.tim) * 24 + to_char(trunc(sysdate) + t1.tim, 'hh24') ||
       to_char(trunc(sysdate) + t1.tim, ':mi:ss')
  from tab1 t1
;

如果在之前的基础上再要求日期差可以为负数，那么应该这样写

with tab1 as (
select to_date('2021/03/02 14:25:01', 'yyyy/mm/dd hh24:mi:ss') - 
       to_date('2021/03/03 16:28:00', 'yyyy/mm/dd hh24:mi:ss') tim
  from dual
)
select decode(sign(t1.tim), -1, '-', '') ||
       (trunc(abs(t1.tim)) * 24 + to_char(trunc(sysdate) + abs(t1.tim), 'hh24')) ||
       to_char(trunc(sysdate) + abs(t1.tim), ':mi:ss')
  from tab1 t1
;

原理：在oracle中日期格式相减的值的单位为天，用这个天数加上各种日期函数就可以得出时分秒格式的结果。

为了使用方便，也可以将上述代码封装到函数中使用。

create or replace function test_0303(p_date_num number)
return varchar2 is 
 v_res varchar2(50);
begin

  select trunc(p_date_num) * 24 + to_char(trunc(sysdate) + abs(p_date_num), 'hh24') ||
         to_char(trunc(sysdate) + abs(p_date_num), ':mi:ss')
         into v_res
    from dual t1
  ;
  return v_res;
end;

通过函数调用

select test_0303(
           to_date('2021/03/03 16:28:00', 'yyyy/mm/dd hh24:mi:ss') -
           to_date('2021/03/03 14:25:01', 'yyyy/mm/dd hh24:mi:ss')
       ) from dual;