-- timestampdiff(interval,dt1,dt2[,isabs]);
-- interval取值为：year,quarter,month,week,day,hour,minute,second
-- 如果dt1 > dt2且isabs为'f'时, 将显示负值
-- 修改于2023-08-25
drop function if exists timestampdiff;
create function timestampdiff(itv varchar(10), dt1 datetime year to fraction(5), dt2 datetime year to fraction(5), isabs boolean default 't')
returning bigint with (not variant);
  define v_value bigint;
  define v_1lt2 boolean;
  define v_interval varchar(30);
  define v_itv varchar(10);
  on exception
    return null;
  end exception;
 
  let v_value = 0;
  let v_itv = lower(itv);
  let v_1lt2 = 'f';
   
  if dt1 > dt2 then
    let v_interval = dt1 - dt2;
    let v_1lt2 = 't';
  else
    let v_interval = dt2 - dt1;
  end if;
  if v_itv = 'day' then
    let v_value = substr(v_interval,1,9)::bigint;
  elif v_itv = 'hour' then
    let v_value = substr(v_interval,1,9)::bigint * 24 + substr(v_interval,11,2)::bigint;
  elif v_itv = 'minute' then
    let v_value = (substr(v_interval,1,9)::bigint * 24 + substr(v_interval,11,2)::bigint) * 60 + substr(v_interval,14,2);
  elif v_itv = 'second' then
    let v_value = ((substr(v_interval,1,9)::bigint * 24 + substr(v_interval,11,2)::bigint) * 60 + substr(v_interval,14,2)) * 60 + substr(v_interval,17,2);
  elif v_itv = 'week' then
    let v_value = substr(v_interval,1,9)::bigint / 7;
  elif v_itv = 'month' then
    let v_value = MONTHS_BETWEEN(dt1,dt2)::bigint;
  elif v_itv = 'quarter' then
    let v_value = MONTHS_BETWEEN(dt1,dt2)::bigint / 3;
  elif v_itv = 'year' then
    let v_value = MONTHS_BETWEEN(dt1,dt2)::bigint / 12;
  end if;
 
  if v_1lt2 = 't' and isabs = 'f' then
    return 0 - v_value;
  else
    return v_value;
  end if;
end function;
