CODEWITHSUNDEEP
sqlpostgresql
Jon
Jon

Reputation: 67

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

Upvotes: 3

Views: 383

Answers (2)

Elad
Elad

Reputation: 894

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269593

For the date, you would use:

select current_date - interval '1 year'

For the date/time:

select now() - interval '1 year'

Upvotes: 2

Related Questions