Re: boolean operator on interval producing strange results

On 2/20/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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.)

you are correct once again, production was recently updated and locale
was not properly set.

The sql in question was already fixed, it was sloppy and I was just
curious what was going on. completely agree regarding implicit
casts...evil! (especially on types like interval)

merlin