select getMedianTime('2021-09-04 06:16:41'::TIMESTAMP,'2021-09-04 07:20:42'::TIMESTAMP)
create or replace function getMedianTime(starttime TIMESTAMP,endtime TIMESTAMP)
returns varchar as $$
declare
p_day integer;
p_hour integer;
p_minute integer;
p_second integer;
sumTime varchar;
begin
sumTime := '';
p_day := (select extract(day FROM (age(endtime, starttime))));
if p_day <> 0 then
sumTime := sumTime || p_day || '天';
else
sumTime := sumTime || '0天';
end if;
p_hour := (select extract(hour FROM (age(endtime, starttime))));
if p_hour <> 0 then
sumTime := sumTime || p_hour || '时';
else
sumTime := sumTime || '0时';
end if;
p_minute := (select extract(minute FROM (age(endtime, starttime))));
if p_minute <> 0 then
sumTime := sumTime || p_minute || '分钟';
else
sumTime := sumTime || '0分钟';
end if;
p_second := (select extract(second FROM (age(endtime, starttime))));
if p_second <> 0 then
sumTime := sumTime || p_second || '秒';
else
sumTime := sumTime || '0秒';
end if;
return sumTime;
end; $$
language plpgsql;