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

Re: timestamp arithmetic (a possible bug?)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: timestamp arithmetic (a possible bug?)
Date: 2004-07-02 13:55:04
Message-ID: 13897.1088776504@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk> writes:
> I saw this behaviour in PostgreSQL 7.2. (Once again, I know this is an old 
> release but I do not have a newer version installed, and I am only using 
> the server for research purposes). If you execute the following statement

> SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004 
> 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);

More recent releases fail with

regression=# SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
ERROR:  operator does not exist: interval + timestamp without time zone
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

(There is a timestamp + interval operator, so you could make it work by
flipping around the outer addition.)

It looks like what is happening in 7.2 is the parser is choosing the
closest available operator, which happens to be time + date, and
then coercing the interval and timestamp to time and date respectively.
The former isn't so bad but the latter loses your 10AM data...

Since then (I think in 7.3) we tightened the rules so that
information-losing coercions, such as timestamp to date, won't be
invoked implicitly.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2004-07-02 13:56:18
Subject: Re: Possible bug?
Previous:From: Ilir GashiDate: 2004-07-02 13:23:55
Subject: Re: timestamp arithmetic (a possible bug?)

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