"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 2/19/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Merlin Moncure" <mmoncure@gmail.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