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
