人大金仓不支持TimeStampDiff 函数

人大金仓不支持TimeStampDiff 函数

1. 安装了金仓需要计算两个日期之间相差的时间，发现金仓不支持这个函数。这时候需要我们自己在数据库创建TimeStampDiff 函数。

create or replace function timestampdiff(para1 text,para2 timestamp,para3 timestamp) return bigint
as
declare
 diff  interval day to second;
 diffs bigint;
 diffm bigint;
 diffh bigint;
 diffd bigint;
 nyy   bigint;  
 ny   bigint;
 nm    bigint;
begin
 diff:=para3-para2;
 diffs:=extract(second from diff);
 diffm:=extract(minute from diff);
 diffh:=extract(hour from diff);
 diffd:=extract(day from diff);
 ny:=extract(year from age(para3,para2));
 nm:=extract(month from age(para3,para2));
 nyy:=extract(year from para3)-extract(year from para2);
 if para1='second'  then
  return ((diffd*24+diffh)*60+diffm)*60+diffs;
 elseif para1='minute' then
  return (diffd*24+diffh)*60+diffm;
 elseif para1='hour' then
  return diffd*24+diffh;
 elseif para1='day' then 
  return diffd;
 elseif para1='month' then
  return ny*12+nm;
 elseif para1='year' then 
  return nyy;
 else 
  raise warning 'para1 not support "%"!',para1;
  return null;
 end if;
end;

2. 这个时候我们创建了TimeStampDiff 函数，就可以使用了。要注意：在金仓中minute 这个要加单引号并且要小写，这个是当参数传进来的。

select timestampdiff('minute','2018-07-01 09:00:00','2018-07-04 12:00:00')