GBase 8s自定义使用timestampdiff()函数

该函数的功能是：按第一个参数interval的值，获取dt1与dt2的日期时间差值。如果dt1 > dt2且isabs为'f'时, 将显示负值。

-- timestampdiff(interval,dt1,dt2[,isabs]);
-- interval取值为：day,hour,minute,second
-- 如果dt1 > dt2且isabs为'f'时, 将显示负值
-- 修改于2020-11-05
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;
  define rc bigint;
  define rc_lt boolean;
  define rc_char varchar(30);

  let rc_lt = 'f';
  if dt1 > dt2 then
    let rc_char = dt1 - dt2;
    let rc_lt = 't';
  else
    let rc_char = dt2 - dt1;
  end if;
  if lower(itv) = 'day' then
    let rc = substr(rc_char,1,9)::bigint;
  elif lower(itv) = 'hour' then
    let rc = substr(rc_char,1,9)::bigint * 24 + substr(rc_char,11,2)::bigint;
  elif lower(itv) = 'minute' then
    let rc = (substr(rc_char,1,9)::bigint * 24 + substr(rc_char,11,2)::bigint) * 60 + substr(rc_char,14,2);
  elif lower(itv) = 'second' then
    let rc = ((substr(rc_char,1,9)::bigint * 24 + substr(rc_char,11,2)::bigint) * 60 + substr(rc_char,14,2)) * 60 + substr(rc_char,17,2);
  else
    let rc = 0;
  end if;

  if rc_lt = 't' and isabs = 'f' then
    return '-' || rc;
  else
    return rc;
  end if;
end function;

示例：

-- 两个日期差值，day
select timestampdiff('DAY', DATE('2020-05-14'), DATE('2020-05-31')) from dual;

结果: 17

-- 两个日期时间差值，second
select timestampdiff('second', to_date('2020-05-14 00:00:00','yyyy-mm-dd hh24:mi:ss'), DATETIME(2020-05-01 00:00:00) YEAR to SECOND,'f') from dual;

结果：-1123200