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?

49331 次浏览
小开
最佳答案

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

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

提交答案