postgres interval添加变量current_setting不工作

Question

set session myconstants.test =  '10';
set session myconstants.testb =  '10 min';
SELECT now()::time - interval concat (current_setting('myconstants.selfName')::varchar,' min');
SELECT now()::time - INTERVAL '10 min';
set session myconstants.test =  '10';
SELECT now()::time - interval current_setting('myconstants.testb')::varchar;
SELECT now()::time - INTERVAL '10 min';

我想在区间函数中添加变量，但current_setting不工作…我怎么能解决它?我使用postgres

Answer 1

您需要将变量值强制转换为间隔:

SELECT now()::time - current_setting('myconstants.testb')::interval

前缀符号interval '....'only

Answer 2

您也可以尝试make_interval

SET session myconstants.test = 10;
SELECT
now() + make_interval(mins => current_setting('myconstants.test')::int);

Answer 3

我发现这个解决方案，操作符不存在:时间戳与时区+整数在PostgreSql。

create function addit(timestamptz,int) returns timestamptz immutable language sql as $$
select $1+ interval '1 hour'*$2
$$;
create operator + (leftarg =timestamptz, rightarg =int, procedure=addit);
create function minusit(timestamptz,int) returns timestamptz immutable language sql as $$
select $1+ interval '-1 hour'*$2
$$;
create operator - (leftarg =timestamptz, rightarg =int, procedure=minusit);

创建+和-操作符，并执行

set session myconstants.testb =  -1;
select start_time ,start_time - current_setting('myconstants.testb')::integer  from besoccer_team bt ;
select start_time ,start_time - current_setting('myconstants.testb')::integer  from besoccer_team bt ;

start_time是时间戳类型

set session myconstants.testb =  -1;
select  now()::timestamp  , now()::timestamp  - current_setting('myconstants.testb')::integer  from besoccer_team bt ;
select  now()::timestamp  , now()::timestamp  - current_setting('myconstants.testb')::integer  from besoccer_team bt ;