Help with syntax for timestamp addition

[Scott Nixon]

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

[Patrick Fiche]

Have a try at this syntax

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

Patrick

-------------------------------------------------------------------------- ----------------- Patrick Fiche
email : pa***********@aqsacom.com
tél : [phone]
-------------------------------------------------------------------------- -----------------

-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Scott Nixon
Sent: lundi 22 novembre 2004 14:56
To: pg***********@postgresql.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

[Ian Barwick]

On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
<pa***********@aqsacom.com> wrote:

Have a try at this syntax

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

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Peter Eisentraut]

Ian Barwick wrote:

On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche

<pa***********@aqsacom.com> wrote:

Have a try at this syntax

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

Just for the record you could write it like this too:
SELECT number
FROM procedures
WHERE date + (numdays || ' days')::interval <= CURRENT_TIMESTAMP;

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

[Scott Nixon]

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

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: Ian Barwick wrote:

On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche

<pa***********@aqsacom.com> wrote:

Have a try at this syntax

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

Just for the record you could write it like this too:
SELECT number
FROM procedures
WHERE date + (numdays || ' days')::interval <= CURRENT_TIMESTAMP;

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 4: Don't 'kill -9' the postmaster

[Tom Lane]

Scott Nixon <sn****@lssi.net> writes:

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. SELECT number
FROM procedures
WHERE date + numdays <= CURRENT_TIMESTAMP; In 7.0 this works with no problem...

(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

[Scott Nixon]

So I think what you probably *really* want is

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

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: Scott Nixon <sn****@lssi.net> writes:

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.

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

In 7.0 this works with no problem...

(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

--
______________________________________
D. Scott Nixon

LSSi Corp.
email: ni***@lssi.net
url: http://www.lssi.net/~snixon
phone: [phone]
fax: [phone]
______________________________________
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster