Question 90163 in PostgreSQL Question posted in
The official documentation can be found here.

postgres interval add variable current_setting not work – Postgresql

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';

i want add variable in interval function,but current_setting not work..how could i solve it?i use postgres

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    i found this solution,operator does not exist: timestamp with time zone + integer in 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);

    create + and - operator ,and execute

    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 is timestamp type

    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 ;

  2. 0

    You need to cast the variable value to an interval:

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

    The prefix notation interval '....' only works with constants following it.

    Login or Signup to reply.
  3. 0

    You can also try make_interval.

    SET session myconstants.test = 10;

SELECT
    now() + make_interval(mins => current_setting('myconstants.test')::int);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search