Re: Bug #484: TIMESTAMP arithmetic insconsistencies

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mp(at)grymmjack(dot)com
Cc: Thomas Lockhart <lockhart(at)fourpalms(dot)org>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #484: TIMESTAMP arithmetic insconsistencies
Date: 2001-10-17 16:38:43
Message-ID: 27352.1003336723@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

pgsql-bugs(at)postgresql(dot)org writes:
> I have found some inconsistencies relating to TIMESTAMP arithmetic.

I dug into this a little, and what seems to be causing the inconsistency
is a surprising implicit coercion.

> select now()-'2001-09-30' where (now()-'2001-09-30') < 50;
>> returned 0 rows

I did this to see how the parser interpreted this expression:

regression=# create view v as select (now()-'2001-09-30') < 50;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (reltime((now() - '2001-09-30 00:00:00-04'::timestamptz)) < (50)::reltime);

Since now()-'2001-09-30' yields an interval, the choice of reltime to do
the comparison is not too surprising (apparently there's an
int4->reltime coercion but not int4->interval). Anyway the result is at
least somewhat sensible: the 50 gets interpreted as 50 seconds.

> select now()-'2001-09-30' where (now()-'2001-09-30') < 50.0;
>> "16 days xx:xx:xx"

regression=# drop view v;
DROP
regression=# create view v as select (now()-'2001-09-30') < 50.0;
CREATE
regression=# \d v
View "v"
Column | Type | Modifiers
----------+---------+-----------
?column? | boolean |
View definition: SELECT (text((now() - '2001-09-30 00:00:00-04'::timestamptz)) < '50'::text);

Text!!?? Well, apparently textlt is the only available operator whose
input types can be reached in one coercion step from both interval and
float8. But it seems a pretty surprising choice anyway. I wonder
whether we have too many implicit coercions to text available.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2001-10-18 07:22:15 Bug #486: tab-complete.o: filename_completion_function reference not found when make'ing either 7.1.2 or 7.1.3 under Windows 2000
Previous Message Tom Lane 2001-10-17 14:40:16 Re: Bug #485: strange behavior when creting rules with serial id