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
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 |