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

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

is there a sql command to change a field data type
from varchar(10) to varchar()?

i tried to do this in pgadmin3, but when i deleted the
length (maximum characters) attribute, the OK button
stayed grayed out and inactive.  as an aside, the OK
button becomes active when the length attribute is
changed...  but turns gray when it is deleted
altogether.

i don't want to put any arbitrary constraints on my
s/n field - especially since there is no performance
benefit in doing so per the manual.

tia...

ps - i can delete and recreate the table with no
problems - but i would prefer to learn something for
future reference, if possible.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On 8/4/05, operationsengineer1@yahoo.com <operationsengineer1@yahoo.com> wrote:
> is there a sql command to change a field data type
> from varchar(10) to varchar()?
>
> i tried to do this in pgadmin3, but when i deleted the
> length (maximum characters) attribute, the OK button
> stayed grayed out and inactive.  as an aside, the OK
> button becomes active when the length attribute is
> changed...  but turns gray when it is deleted
> altogether.
>
> i don't want to put any arbitrary constraints on my
> s/n field - especially since there is no performance
> benefit in doing so per the manual.
>
> tia...
>
> ps - i can delete and recreate the table with no
> problems - but i would prefer to learn something for
> future reference, if possible.
>

use text datatype, instead


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

In 8.0 and above, this can be done by using
 ALTER TABLE table_name alter column column_name type new_data_type

for pre 8.0  releases
create an additional column(with different name) with the desired data type(higher data type)
update the column with the values of the old column
remove the old column.
Finally rename the new column to old column.




--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

--- Jaime Casanova <systemguards@gmail.com> wrote:

> On 8/4/05, operationsengineer1@yahoo.com
> <operationsengineer1@yahoo.com> wrote:
> > is there a sql command to change a field data type
> > from varchar(10) to varchar()?
> >
> > i tried to do this in pgadmin3, but when i deleted
> the
> > length (maximum characters) attribute, the OK
> button
> > stayed grayed out and inactive.  as an aside, the
> OK
> > button becomes active when the length attribute is
> > changed...  but turns gray when it is deleted
> > altogether.
> >
> > i don't want to put any arbitrary constraints on
> my
> > s/n field - especially since there is no
> performance
> > benefit in doing so per the manual.
> >
> > tia...
> >
> > ps - i can delete and recreate the table with no
> > problems - but i would prefer to learn something
> for
> > future reference, if possible.
> >
>
> use text datatype, instead


worked like a charm.  are there any advantages to
using text of varchar() or vice versa?  are they equal
and just personal preference?



____________________________________________________
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs

On Thu, Aug 04, 2005 at 09:52:50 -0600,
  "Walker, Jed S" <Jed_Walker@cable.comcast.com> wrote:
>
> 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.

Use something like:
Where last_date < now() - Interval * '1 day'::interval