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

深藏阁楼爱情的钟 2021-09-25 05:06 514阅读 0赞

要求：  
计算`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;