[SQL] 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: [SQL] Create interval using column value?
Larry Lennhoff <[EMAIL PROTECTED]> 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: [SQL] 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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED])
Re: [SQL] 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
