Re: Help with syntax for timestamp addition

Have a try at this syntax

SELECT number
FROM procedures
WHERE date + CAST( numdays || ' days' AS interval ) <= CURRENT_TIMESTAMP;

Patrick

> --------------------------------------------------------------------------
-----------------
> Patrick Fiche
> email : patrick(dot)fiche(at)aqsacom(dot)com
> tél : 01 69 29 36 18
> --------------------------------------------------------------------------
-----------------
>
>
>

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Scott Nixon
Sent: lundi 22 novembre 2004 14:56
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Help with syntax for timestamp addition

New to Postgres 7.3 from 7.0.

Am having some trouble with a query that worked in 7.0 but not in
7.3.....can't seem to figure out the syntax or find info about how to do
this anywhere.

Consider for the following query:
- 'number' is an integer
- 'procedures' is the table name
- 'date' is a timestamp
- 'numdays' is an integer

SELECT number
FROM procedures
WHERE date + numdays <= CURRENT_TIMESTAMP;

In 7.0 this works with no problem...finding all rows where the date plus
some number of days is less than the current. But in 7.3 I get:

ERROR: Unable to identify an operator '+' for types 'timestamp without
time zone' and 'integer'
You will have to retype this query using an explicit cast

I've never had to create casts before so I'm not too sure how to work
this casting into the query....keep getting various syntax errors no
matter what I try. If I try to incorporate intervals, I also get errors.
I just can't seem to find good examples in any documentation.

Any help is appreciated.

-Scott

---------------------------(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

Protected by Polesoft Lockspam
http://www.polesoft.com/refer.html