Thread: Create interval using column value?

Hi

I have a table (A) with a integer column called build_interval, which 
contains the number of milliseconds betweeen builds.  I have another table 
(B) with a timestamp with timezone column called built_on.  The two tables 
share a column called join_col.  What I want to do is find all the rows in 
A which were built before now() - build_interval seconds ago.  If I could 
just write this in SQL it would look something like:

SELECT A.id FROM A JOIN B ON (join_col) WHERE built_on < now() - interval 
'build_interval seconds';  This does not work at all - I cannot find a way 
to build an interval using a column - casting an integer as an interval is 
forbidden. Can anyone suggest a way to achieve my desired result? I'm not 
wedded to the interval approach.  I can probably change the column type of 
build_interval if necessary.

I am using postgres 7.3.4

Thanks in advance for any help.

Larry Lennhoff <llennhoff-postgres@pexicom.com> writes:
> What I want to do is find all the rows in 
> A which were built before now() - build_interval seconds ago.  If I could 
> just write this in SQL it would look something like:

> SELECT A.id FROM A JOIN B ON (join_col) WHERE built_on < now() - interval 
> 'build_interval seconds';

You are confused about the difference between a literal constant and an
expression.   Try something like

... WHERE built_on < now() - build_interval * interval '1 second';

which relies on the number-times-interval operator.
        regards, tom lane

On Oct 25, 2004, at 11:54 AM, Larry Lennhoff wrote:

> SELECT A.id FROM A JOIN B ON (join_col) WHERE built_on < now() - 
> interval 'build_interval seconds';

It would help to see the error you're getting, but I suspect it has 
something to do with the fact that you're quoting 'build_interval'. Try 
something like WHERE built_on < current_timestamp - build_interval *  
INTERVAL '0.001 second';
(btw, current_timestamp is the SQL standard for now() )

Regards,

Michael Glaesemann
grzm myrealbox com

Thanks, that was exactly what I needed.

Larry

]At 11:11 PM 10/24/2004, Tom Lane wrote:
>Larry Lennhoff <llennhoff-postgres@pexicom.com> writes:
> > What I want to do is find all the rows in
> > A which were built before now() - build_interval seconds ago.  If I could
> > just write this in SQL it would look something like:
>
> > SELECT A.id FROM A JOIN B ON (join_col) WHERE built_on < now() - interval
> > 'build_interval seconds';
>
>You are confused about the difference between a literal constant and an
>expression.   Try something like
>
>.. WHERE built_on < now() - build_interval * interval '1 second';
>
>which relies on the number-times-interval operator.
>
>                         regards, tom lane