Re: Help with syntax for timestamp addition - Mailing list pgsql-general

From Scott Nixon
Subject Re: Help with syntax for timestamp addition
Date
Msg-id 1101141876.23458.26.camel@talon
Whole thread Raw
In response to Re: Help with syntax for timestamp addition  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
> 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@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
--
______________________________________
D. Scott Nixon

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


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Help with syntax for timestamp addition
Next
From: "Kall, Bruce A."
Date:
Subject: SELECT duplicates in a table