Using a variable period in an interval in Postgres

Question

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?

Aspic

Answer 1

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.

Conciliate

Or: endDate - '1 mon'::interval * $3 (less casting & concatenating) – Tridactyl

@Conciliate Cool :) Remember to @reply in comments or I may miss your answer. – Tridactyl

The multiplication with the interval is a very nice solution! – Piraeus

Answer 2

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;

Frodeen

Answer 3

While the above accepted answer is fine, it's a little bit antiquated - requiring a bit more mental energy to read than needed if you're running on Postgres 9.4+.

Old Way (Postgres Versions < 9.4)

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

New Way (Postgres 9.4+)

startDate TIMESTAMP := endDate - MAKE_INTERVAL(MONTHS => $3);

If you are on Postgres 9.4+, the new MAKE_INTERVAL() function seems much more readable - probably why they created it.

If you want something you can run in your editor, here are a couple of examples (I substituted the original variable binding $3 with the number 2 for an example of 2-months prior to the current date).

SELECT CURRENT_DATE - 2 * INTERVAL '1 MONTH';

SELECT CURRENT_DATE - MAKE_INTERVAL(MONTHS => 2);

Reorder

Answer 4

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.

Peag

Answer 5

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

Salify