Jon Jon - 6 months ago 17
SQL Question

Postgresql current datetime for past year

Is there a function on postgresql that will let me get the current datetime for the past year or x number of past years?
I know i can do this

select now() - interval '1 year';
but in a function how can i put the number of years in a variable

x := '2 year'
Is it possible to do this select now() - interval x;


I tried but it give me error

Answer

If you want to use variable you can do this:

CREATE OR REPLACE FUNCTION func(input integer)
  RETURNS TIMESTAMP WITHOUT TIME ZONE  AS
  $BODY$ 
  declare 
        result TIMESTAMP WITHOUT TIME ZONE; 
  begin
        select  now() - (input  || ' years')::interval into result;
        return result;
  end;
  $BODY$
  LANGUAGE plpgsql VOLATILE