Re: boolean operator on interval producing strange results

"Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> On 2/19/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
>>> # select ((now() - '1 day'::interval)::timestamp - now()) < 0;
>>> ?column?
>>> ----------
>>> f <-- looks busted to me
>>> (1 row)
>>
>> If you'd casted to timestamptz then I'd agree this is busted.
>> As-is, it might have something to do with your timezone setting,
>> which you didn't mention?

> show timezone reports us/eastern in both cases.

Oooohhh ... it's not timezone, it's locale. EXPLAIN, when used
correctly, shows how the system is interpreting this, and it's
not what you think:

regression=# explain select 1 where ((now() - '1 day'::interval)::timestamp - now()) < 0;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.02..0.03 rows=1 width=0)
One-Time Filter: ((((((now() - '1 day'::interval))::timestamp without time zone)::timestamp with time zone - now()))::text < '0'::text)
(2 rows)

Still another demonstration of why implicit casts to text are evil :-(
Try putting the '0' in quotes. (And drop the useless explicit cast
to timestamp while you're at it.)

regards, tom lane