PostgreSql中的timestampdiff
近期项目从Mysql数据库，迁移的到PostgreSql数据库，其中代码中很多timestampdiff 在PostgreSql缺少对应的函数。所以自己整理了一份
create or REPLACE FUNCTION timestampdiff(HOUR text,create_time TIMESTAMP,end_time TIMESTAMP)
RETURNS BIGINT
as
$$
BEGIN
if upper($1)='SECOND' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)) )::bigint;
end if;
if UPPER($1)='HOUR' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600) )::bigint;
end if;
if upper($1)='DAY' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24) )::bigint;
end if;
if upper($1)='MONTH' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24/30) )::bigint;
end if;
if upper($1)='YEAR' then
return trunc(extract(EPOCH FROM ($3 - $2::TIMESTAMP)/3600/24/365) )::bigint;
end if;
end;
$$
LANGUAGE plpgsql