Re: Help with syntax for timestamp addition

Scott Nixon <snixon@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