Thread: boolean operator on interval producing strange results
We updated our production server to postgresql 8.2.3 yesterday. This query is giving different results than on our development box: development: # select ((now() - '1 day'::interval)::timestamp - now()) < 0; ?column? ---------- t (1 row) production # select ((now() - '1 day'::interval)::timestamp - now()) < 0; ?column? ---------- f <-- looks busted to me (1 row) both servers are redhat fc4, same version postgresql. only difference I know of is development is a little behind on yum update. can anybody think of anything that might have influenced this? merlin
"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?
regards, tom lane
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. also, i don't really
see how this matters, since we are comparing '-1 days'::interval with
0 in both cases. in fact:
# show timezone;
TimeZone
------------
US/Eastern
(1 row)
# select ('-1 days'::interval) < 0;
?column?
----------
f
(1 row)
as it happens, after months and months of faithful service, this
machine decided to dump core last night. so, we are scheduling some
downtime + yum update. (my previous mail was wrong, production was the
non-updated box). this is the only environmental difference I can
think of. At the very least I can report back if this fixes the
problem.
merlin
All of these statements produce 'f' for me as well, via 8.2.1 on RHEL 4.
select ((now() - '1 day'::interval)::timestamp - now()) < 0;
select ((now() - '1 day'::interval)::timestamptz - now()) < 0;
select ('-1 days'::interval) < 0;
But all of these return 't':
select ((now() - '1 day'::interval)::timestamp - now()) < '0'::interval;
select ((now() - '1 day'::interval)::timestamptz - now()) <
'0'::interval;
select ('-1 days'::interval) < '0'::interval;
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Merlin Moncure
Sent: Tuesday, February 20, 2007 8:30 AM
To: Tom Lane
Cc: postgres general
Subject: Re: [GENERAL] boolean operator on interval producing strange
results
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. also, i don't really
see how this matters, since we are comparing '-1 days'::interval with
0 in both cases. in fact:
# show timezone;
TimeZone
------------
US/Eastern
(1 row)
# select ('-1 days'::interval) < 0;
?column?
----------
f
(1 row)
as it happens, after months and months of faithful service, this
machine decided to dump core last night. so, we are scheduling some
downtime + yum update. (my previous mail was wrong, production was the
non-updated box). this is the only environmental difference I can
think of. At the very least I can report back if this fixes the
problem.
merlin
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
"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
On 2/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "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.) 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