Re: Help with syntax for timestamp addition

> 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 <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
--
______________________________________
D. Scott Nixon

LSSi Corp.
email: nixon(at)lssi(dot)net
url: http://www.lssi.net/~snixon
phone: (919) 466-6834
fax: (919) 466-6810
______________________________________