Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-10-18 07:22:15
Subject: 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:From: Tom LaneDate: 2001-10-17 14:40:16
Subject: Re: Bug #485: strange behavior when creting rules with serial id

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group