oracle计算两个时间的差值（XX天XX时XX分XX秒）

在工作中需要计算两个时间的差值，结束时间 – 开始时间，又不想在js里写function，也不想在java里去计算，干脆就在数据库做了一个函数来计算两个时间的差值。格式为xx天xx时xx分xx秒；

上代码：

create or replace function f_get_diff_time(start_time in date,
                                           end_time   in date)
  return varchar2 is
  diff_time varchar2(50);
begin
  select tday || '天' || thour || '时' || tminute || '分' || round((tt - tminute) * 60) || '秒' into diff_time
    from (select tday,
                 thour,
                 trunc((tt - thour) * 60) tminute,
                 (tt - thour) * 60 tt
            from (select tday,
                         trunc((tt - tday) * 24) thour,
                         (tt - tday) * 24 tt
                    from (select to_number(end_time - start_time) as tt,
                                 trunc(to_number(end_time - start_time)) as tday
                            from (select start_time, end_time from dual))));

  return diff_time;
end;

输出的格式为上图；