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

Have a try at this syntax

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

Patrick


-----Original Message-----
From: pgsql-general-owner (AT) postgresql (DOT) org
[mailtogsql-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


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

Cool! Thanks....that works perfectly.

-Scott





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

On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
<patrick.fiche (AT) aqsacom (DOT) com> wrote:
Just for the record you could write it like this too:
SELECT number
FROM procedures
WHERE date + (numdays || ' days')::interval <= CURRENT_TIMESTAMP;

Ian Barwick

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Ian Barwick wrote:
Just to add to the record, the mathematically sound way to write this
query would be this:

SELECT number
FROM procedures
WHERE date + numdays * interval '1 day' <= current_timestamp;

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

Thanks for that Peter! That's a lot closer than what I originally
had...I didn't think about doing that but it makes sense.


Is there any advantage/disadvantages to using this method or the other?




On Mon, 2004-11-22 at 10:26, Peter Eisentraut wrote:





---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Scott Nixon <snixon (AT) lssi (DOT) net> writes:
(Tries it...) The reason it worked in 7.0 was that 7.0 would implicitly
down-convert the timestamp value to a value of type date, and then apply
the date-plus-integer operator. The operator is still there, but later
versions are less willing to apply information-losing type coercions
implicitly. So the exact equivalent of what you were doing before is

.... WHERE CAST(date AS date) + numdays <= CURRENT_TIMESTAMP;

The comparison portion of this will require an up-conversion from date
back to timestamp, which is inefficient and pointless (not to mention
that it exposes you to daylight-savings-transition issues, because
CURRENT_TIMESTAMP is timestamp with time zone). So I think what you
probably *really* want is

.... WHERE CAST(date AS date) + numdays <= CURRENT_DATE;

which keeps both the addition and the comparison as simple date
operations with no sub-day resolution and no timezone funnies.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Thanks Tom! Yeah, I guess you are right on that point. I hadn't thought about that.
The implementation of this wouldn't be affected since this query is buried in a script
that runs out of cron once a day, but I suppose I might as well do it right if I'm
going to do it.



On Mon, 2004-11-22 at 11:31, Tom Lane wrote:
______________________________________
D. Scott Nixon

LSSi Corp.
email: nixon (AT) lssi (DOT) net
url: http://www.lssi.net/~snixon
phone: (919) 466-6834
fax: (919) 466-6810
______________________________________


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster