kingbase人大金仓计算时间差-526互联

select timestampdiff('minute','2023-04-17 10:00:00',now()) 
select timestampdiff('day','2023-04-10 10:00:00',now()) 
select timestampdiff('month','2023-03-10 10:00:00',now())
select timestampdiff('year','2012-03-10 10:00:00',now())

需要计算两个日期之间相差的时间，人大金仓不支持TimeStampDiff 函数，需要我们自己在数据库创建TimeStampDiff 函数。

要注意：在金仓中minute 这个要加单引号并且要小写，这个是当参数传进来的。

数据库函数代码如下：

 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;