[GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread 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 querykeep 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

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread 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 : [EMAIL PROTECTED]
 tél : 01 69 29 36 18
 --
-





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Scott Nixon
Sent: lundi 22 novembre 2004 14:56
To: [EMAIL PROTECTED]
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 querykeep 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

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread Scott Nixon 

 Have a try at this syntax
 
 SELECT number
 FROM procedures
 WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP;
 
 Patrick
 

Cool!  Thanksthat works perfectly.  

-Scott





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread Ian Barwick 
On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche
[EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread Peter Eisentraut 
Ian Barwick wrote:
 On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche

 [EMAIL PROTECTED] 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

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread 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
 
  [EMAIL PROTECTED] 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

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread Tom Lane 
Scott Nixon [EMAIL PROTECTED] 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

Re: [GENERAL] Help with syntax for timestamp addition

2004-11-22 Thread 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 [EMAIL PROTECTED] 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:   [EMAIL PROTECTED]
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