postgres通过开始时间,结束时间获得 几天几小时几分钟几秒

纯自己写的,编写不易,请给个大大的赞吧!

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;

本站持续分享优质内容

请Ctrl+D把本站变为收藏,下次访问不迷路呀~

点赞
  1. 说道:
    Google Chrome Windows 7
    1

发表评论

电子邮件地址不会被公开。必填项已用 * 标注