PostgreSQL数据库datediff函数的创建

最新推荐文章于 2025-02-11 09:14:02 发布

原创最新推荐文章于 2025-02-11 09:14:02 发布 · 824 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #postgresql

PostgreSQL函数专栏收录该内容

9 篇文章

订阅专栏

创建datediff函数

-- FUNCTION: datediff(timestamp with time zone, timestamp with time zone)

-- DROP FUNCTION IF EXISTS datediff(timestamp with time zone, timestamp with time zone);

CREATE OR REPLACE FUNCTION datediff(
	endtime timestamp with time zone,
	starttime timestamp with time zone)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_result   numeric;
begin        
 l_result=date_part('day',endTime-startTime);
--    l_result:=floor(endTime-startTime);        
    return   l_result;
end;
$BODY$;

ALTER FUNCTION datediff(timestamp with time zone, timestamp with time zone)
    OWNER TO postgres;
	
CREATE OR REPLACE FUNCTION datediff(
endtime timestamp with time zone,
starttime text)
RETURNS numeric
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_result   numeric;
begin        
 l_result=date_part('day',endTime-starttime::timestamp with time zone);
--    l_result:=floor(endTime-startTime);        
    return   l_result;
end;
$BODY$;

CREATE OR REPLACE FUNCTION datediff(
endtime text,
starttime timestamp with time zone)
RETURNS numeric
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_result   numeric;
begin        
 l_result=date_part('day',endTime::timestamp with time zone-starttime);
--    l_result:=floor(endTime-startTime);        
    return   l_result;
end;
$BODY$;

CREATE OR REPLACE FUNCTION datediff(
endtime text,
starttime text)
RETURNS numeric
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
l_result   numeric;
begin        
 l_result=date_part('day',endTime::timestamp with time zone-starttime::timestamp with time zone);
--    l_result:=floor(endTime-startTime);        
    return   l_result;
end;
$BODY$;

测试

select * from t_rule;

select "id",datediff(,"updated_at","created_at") from t_rule;

select datediff('2023-10-07','2023-10-05');
select datediff('2023-10-07'::timestamp,'2023-10-05'::timestamp);
select datediff('2023-10-05'::timestamp,'2023-10-10'::timestamp);
select datediff('2023-10-05','2023-10-10'::timestamp);
select datediff('2023-10-07'::timestamp,'2023-10-05');

-- select datediff('month','2023-11-02','2022-09-01');

-- select date_part('month','2023-11-02'::timestamp with time zone-2022-09-01::timestamp with time zone);