Thread: using interval in a query with a column for the interval value?

(I’m sending this again, because the original somehow got split into two posts)

I am writing a script and I need to output results to a file based on a table where the data to select is based on a ‘last’ date and an ‘interval’.

Essentially

Name

Last_date

Interval

Jed     2005-06-02      30

Tom     2005-08-02      30

Select name

From table1

Where last_date < now() – [[interval days]];

The interval days part is what is stumping me I need to say “now() – interval ’30 days’” but I need to use the interval column.

How do I make this work?

Thanks,

Jed

On Thu, Aug 04, 2005 at 01:23:42PM -0600, Walker, Jed S wrote:
> Name
> Last_date
> Interval
>
> Jed    2005-06-02    30
> Tom    2005-08-02    30
>
> Select name
> From table1
> Where last_date < now() - [[interval days]];
>
> The interval days part is what is stumping me I need to say "now() -
> interval '30 days'" but I need to use the interval column.

See "Date/Time Functions and Operators" in the documentation:

http://www.postgresql.org/docs/8.0/static/functions-datetime.html

Maybe this example is what you're looking for (I've changed the
name of the "interval" column to avoid confusion with the interval
type):

CREATE TABLE table1 (
    name       text,
    last_date  date,
    numdays    integer
);

INSERT INTO table1 VALUES ('Jed', '2005-06-02', 30);
INSERT INTO table1 VALUES ('Tom', '2005-08-02', 30);

SELECT * FROM table1 WHERE last_date < current_date - numdays;
 name | last_date  | numdays
------+------------+---------
 Jed  | 2005-06-02 |      30
(1 row)

In the general case you can multiply a numeric type by an interval:

SELECT now(), now() - 1.5 * '1 day'::interval;
              now              |           ?column?
-------------------------------+-------------------------------
 2005-08-04 14:02:57.109946-06 | 2005-08-03 02:02:57.109946-06
(1 row)

--
Michael Fuhr

That's the document I was using. I got it working now, it wouldn't work
using "now()" but with "current_date" it works fine.

Thanks!

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Thursday, August 04, 2005 2:05 PM
To: Walker, Jed S
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] using interval in a query with a column for the
interval value?

On Thu, Aug 04, 2005 at 01:23:42PM -0600, Walker, Jed S wrote:
> Name
> Last_date
> Interval
>
> Jed    2005-06-02    30
> Tom    2005-08-02    30
>
> Select name
> From table1
> Where last_date < now() - [[interval days]];
>
> The interval days part is what is stumping me I need to say "now() -
> interval '30 days'" but I need to use the interval column.

See "Date/Time Functions and Operators" in the documentation:

http://www.postgresql.org/docs/8.0/static/functions-datetime.html

Maybe this example is what you're looking for (I've changed the
name of the "interval" column to avoid confusion with the interval
type):

CREATE TABLE table1 (
    name       text,
    last_date  date,
    numdays    integer
);

INSERT INTO table1 VALUES ('Jed', '2005-06-02', 30);
INSERT INTO table1 VALUES ('Tom', '2005-08-02', 30);

SELECT * FROM table1 WHERE last_date < current_date - numdays;
 name | last_date  | numdays
------+------------+---------
 Jed  | 2005-06-02 |      30
(1 row)

In the general case you can multiply a numeric type by an interval:

SELECT now(), now() - 1.5 * '1 day'::interval;
              now              |           ?column?
-------------------------------+-------------------------------
 2005-08-04 14:02:57.109946-06 | 2005-08-03 02:02:57.109946-06
(1 row)

--
Michael Fuhr