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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Create interval using column value?

Larry Lennhoff 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Create interval using column value?

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Create interval using column value?

Thanks, that was exactly what I needed.

Larry

]At 11:11 PM 10/24/2004, Tom Lane wrote:
>Larry Lennhoff 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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)