From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Scott Nixon <snixon(at)lssi(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help with syntax for timestamp addition |
Date: | 2004-11-22 16:31:24 |
Message-ID: | 16114.1101141084@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Scott Nixon <snixon(at)lssi(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Nixon | 2004-11-22 16:44:36 | Re: Help with syntax for timestamp addition |
Previous Message | Tom Lane | 2004-11-22 16:00:28 | Re: Oid to text... |