Oracle自定义函数计算时间间隔

将分钟数转换为小时分钟的字符串格式显示

CREATE OR REPLACE Function fun_convert_minutetochar(n_minute number)
  RETURN varchar2 Is
  ls_between   varchar2(20);
  li_hours     number;
  li_minute    number;
Begin
  li_minute := n_minute;
  if li_minute >= 60 then
    li_hours := trunc(li_minute/60);
    li_minute := li_minute - li_hours * 60;
    ls_between :=to_char(li_hours)||'小时'||to_char(li_minute)||'分';
  elsif li_minute<60 and li_minute > 0 then
    ls_between :=to_char(li_minute)||'分';
  else
    ls_between :='';
  end if;
  return ls_between;
END fun_convert_minutetochar;

计算得到另个时间之间的分钟数

CREATE OR REPLACE Function fun_get_twodate_minute_between(sdate Date, edate Date)
  RETURN number Is
  li_minute    number;
Begin
  select round((edate-sdate)*24*60) into li_minute from dual;

  return li_minute;
END fun_get_twodate_minute_between;

合并

CREATE OR REPLACE Function fun_get_twodate_between(sdate Date, edate Date)
  RETURN varchar2 Is
  ls_between   varchar2(20);
  li_hours     number;
  li_minute    number;

Begin
  select round((edate-sdate)*24*60) into li_minute from dual;
  if li_minute >= 60 then
    li_hours := trunc(li_minute/60);
    li_minute := li_minute - li_hours * 60;
    ls_between :=to_char(li_hours)||'小时'||to_char(li_minute)||'分';
  elsif li_minute<60 and li_minute > 0 then
    ls_between :=to_char(li_minute)||'分';
  else
    ls_between :='';
  end if;
  return ls_between;
END fun_get_twodate_between;