Using a variable period in an interval in Postgres

I have a relation that maintains monthly historical data. This data is added to the table on the last day of each month. A service I am writing can then be called specifying a month and a number of months prior for which to retrieve the historical data. I am doing this by creating startDate and endDate variables, and then returning data between the two. The problem I am having is that startDate is a variable number of months before endDate, and I cannot figure out how to use a variable period in an interval.

Here is what I have:

    DECLARE
      endDate   TIMESTAMP := (DATE_TRUNC('MONTH',$2) + INTERVAL '1 MONTH') - INTERVAL '1 DAY';
      startDate TIMESTAMP := endDate - INTERVAL $3 'MONTH';

I know that the line for startDate is not correct. How is this properly done?

Use this line:

startDate TIMESTAMP := endDate - ($3 || ' MONTH')::INTERVAL;

and note the space before MONTH. Basically: You construct a string with like 4 MONTH and cast it with ::type into a proper interval.

Edit: I' have found another solution: You can calculate with interval like this:

startDate TIMESTAMP := endDate - $3 * INTERVAL '1 MONTH';

This looks a little bit nicer to me.

This code has nothing directly to do with your situation, but it does illustrate how to use variables in INTERVAL arithmetic. My table's name is "calendar".

CREATE OR REPLACE FUNCTION test_param(num_months integer)
  RETURNS SETOF calendar AS
$BODY$

    select * from calendar
    where cal_date <= '2008-12-31 00:00:00'
    and cal_date > date '2008-12-31' - ($1 || ' month')::interval;

$BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;

The most readable way I have found to pass a variable time period to Postgres is similar to A.H.'s answer: by multiplying by an integer. But this can be done without a cast.

Python example (with sqlalchemy and pandas):

import pandas as pd
import sqlalchemy as sa

connection = sa.create_engine(connection_string)

df = pd.read_sql(
   sa.text('''
       select * from events
       where
       event_date between now() - (interval '1 day' * :ndays) and now()
       limit 100;
'''),
   connection,
   params={'ndays': 100}
)

The number of days (ndays) is passed as an integer from within Python - so unintended consequences are less likely.

My approach is like this.. It gives me option to set specific date or a relative range.

create or replace function search_data(_time_from timestamptz default null, _last_interval text default null)
    returns setof journal
    language plpgsql as
$$
begin
    return query
        select *
        from journal
        where created >= case
                             when _time_from is not null
                                 then _time_from
                             else now() - _last_interval::interval end;
end;
$$;