Re: 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