Using a variable period in an interval in Postgres

postgresql variables intervals period
Belizzle picture Belizzle · Oct 17, 2011 · Viewed 35.7k times · Source

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?

Answer

A.H. picture A.H. · Oct 17, 2011

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.

Related questions

How do you use variables in a simple PostgreSQL script?

For example, in MS-SQL, you can open up a query window and run the following: DECLARE @List AS VARCHAR(8) SELECT @List = 'foobar' SELECT * FROM dbo.PubLists WHERE Name = @List How is this done in PostgreSQL? Can it be done?

Read More
How do you use script variables in psql?

In MS SQL Server, I create my scripts to use customizable variables: DECLARE @somevariable int SELECT @somevariable = -1 INSERT INTO foo VALUES ( @somevariable ) I'll then change the value of @somevariable at runtime, depending on the value that I want in …

Read More
Loop on tables with PL/pgSQL in Postgres 9.0+

I want to loop through all my tables to count rows in each of them. The following query gets me an error: DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY …

Read More