Re: timestamp arithmetic (a possible bug?)

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

2 июля 2004 г. в 10:55:06

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

Re: timestamp arithmetic (a possible bug?)

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

2 июля 2004 г. в 11:28:49

Stephan Szabo  writes:
> On Fri, 2 Jul 2004, Tom Lane wrote:
>> (There is a timestamp + interval operator, so you could make it work by
>> flipping around the outer addition.)

> Should we be providing an interval + timestamp operator as well since it
> looks like the spec implies both orderings should work?

If you see spec support for it, then yes ... where do you read that
exactly?

			regards, tom lane

Re: timestamp arithmetic (a possible bug?)

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

2 июля 2004 г. в 13:35:04

Stephan Szabo  writes:
>>> Should we be providing an interval + timestamp operator as well since it
>>> looks like the spec implies both orderings should work?
>> 
>> If you see spec support for it, then yes ... where do you read that
>> exactly?

> SQL92 (draft) 4.5.3 Operators involving datetimes and intervals (the table
> appears to be the same in SQL99 4.7.3)

Yeah.  It looks like we have most of these, but would need to add
	interval + date
	interval + timetz
	interval + timestamp
	interval + timestamptz
and for consistency
	integer + date
Curiously, we do have interval + time without time zone ... I guess Tom
Lockhart overlooked these when he was working in the area.

I notice also that date - date yields an integer (ie, number of days)
where I think that strict spec compliance would mandate yielding an
interval instead.  I'm uneager to change this though.

			regards, tom lane

Re: timestamp arithmetic (a possible bug?)

От:

Stephan Szabo <sszabo@megazone.bigpanda.com>

Дата:

2 июля 2004 г. в 11:13:56

On Fri, 2 Jul 2004, Tom Lane wrote:

> Ilir Gashi  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);
>
> (There is a timestamp + interval operator, so you could make it work by
> flipping around the outer addition.)

Should we be providing an interval + timestamp operator as well since it
looks like the spec implies both orderings should work?

Re: timestamp arithmetic (a possible bug?)

От:

Stephan Szabo <sszabo@megazone.bigpanda.com>

Дата:

2 июля 2004 г. в 11:40:08

On Fri, 2 Jul 2004, Tom Lane wrote:

> Stephan Szabo  writes:
> > On Fri, 2 Jul 2004, Tom Lane wrote:
> >> (There is a timestamp + interval operator, so you could make it work by
> >> flipping around the outer addition.)
>
> > Should we be providing an interval + timestamp operator as well since it
> > looks like the spec implies both orderings should work?
>
> If you see spec support for it, then yes ... where do you read that
> exactly?

SQL92 (draft) 4.5.3 Operators involving datetimes and intervals (the table
appears to be the same in SQL99 4.7.3)

The list given there is:
 datetime - datetime -> interval
 datetime +,- interval -> datetime
 interval + datetime -> datetime
 interval +,- interval -> interval
 interval *,/ numeric -> interval
 numeric * interval -> interval

Re: timestamp arithmetic (a possible bug?)

От:

Theodore Petrosky <tedpet5@yahoo.com>

Дата:

2 июля 2004 г. в 08:23:06

Interesting....

If I reverse the order it works...

agencysacks=# SELECT CAST('01.01.2004 10:00:00' AS
TIMESTAMP) + (CAST('01.01.2004 10:01:00' AS TIMESTAMP)
- CAST('01.01.2004 10:00:00' AS TIMESTAMP)) as answer;
       answer        
---------------------
 2004-01-01 10:01:00
(1 row)

However your original...


agencysacks=# 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.


agencysacks=# select version();
                                                      
  version                                             
           
-------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.2 on powerpc-apple-darwin7.2.0,
compiled by GCC gcc (GCC) 3.3 20030304 (Apple
Computer, Inc. build 1495)
(1 row)

Looks like postgresql demands the  order to be 
timestamp +- interval.

Ted


--- Ilir Gashi  wrote:
> Hi,
> 
> 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);
> 
> The result returned is: 
> 
>       ?column?
> ---------------------
>  2004-01-01 00:01:00
> (1 row)
> 
> I was expecting: 2004-01-01 10:01:00.
> 
> Tried it on Oracle 8.0.5:
> 
> SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM.YYYY
> HH:MI:SS') - 
> TO_DATE('01.01.2004 10:00:00', 'DD.MM.YYYY
> HH:MI:SS') + TO_DATE('01.01.2004 
> 10:00:00', 'DD.MM.YYYY HH:MI:SS') FROM DUAL;
> 
> --------------------------- 
> 2004-01-01 10:01:00
> (1 row  selected) 
> 
> 
> And MSSQL 7:
> 
> SELECT (CAST('01.01.2004 10:01:00' AS DATETIME) -
> CAST('01.01.2004 
> 10:00:00' AS DATETIME) + CAST('01.01.2004 10:00:00'
> AS DATETIME));
> 
> 
> --------------------------- 
> 
> 2004-01-01 10:01:00.000
> 
> (1 row(s) affected)
> 
> 
> Is this a bug? Same thing happens if I use
> TimestampTZ rather than 
> Timestamp.
> 
> Best regards,
> 
> Ilir
> 
> ____________________________________________
> 
> Ilir Gashi 
> PhD Student 
> Centre for Software Reliability 
> City University 
> Northampton Square, London EC1V 0HB
> email: i.gashi@city.ac.uk
> website:
> http://www.csr.city.ac.uk/csr_city/staff/gashi/
> ____________________________________________
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
> 


		
__________________________________
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

timestamp arithmetic (a possible bug?)

От:

Ilir Gashi <I.Gashi@city.ac.uk>

Дата:

2 июля 2004 г. в 07:54:34

Hi,

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

The result returned is: 

      ?column?
---------------------
 2004-01-01 00:01:00
(1 row)

I was expecting: 2004-01-01 10:01:00.

Tried it on Oracle 8.0.5:

SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM.YYYY HH:MI:SS') - 
TO_DATE('01.01.2004 10:00:00', 'DD.MM.YYYY HH:MI:SS') + TO_DATE('01.01.2004 
10:00:00', 'DD.MM.YYYY HH:MI:SS') FROM DUAL;

--------------------------- 
2004-01-01 10:01:00
(1 row  selected) 


And MSSQL 7:

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


--------------------------- 

2004-01-01 10:01:00.000

(1 row(s) affected)


Is this a bug? Same thing happens if I use TimestampTZ rather than 
Timestamp.

Best regards,

Ilir

____________________________________________

Ilir Gashi 
PhD Student 
Centre for Software Reliability 
City University 
Northampton Square, London EC1V 0HB
email: i.gashi@city.ac.uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________

Re: timestamp arithmetic (a possible bug?)

От:

Ilir Gashi <I.Gashi@city.ac.uk>

Дата:

2 июля 2004 г. в 10:24:50

On Jul 2 2004, Achilleus Mantzios wrote:

> SELECT '01.01.2004 10:00:00'::timestamp + ('01.01.2004
> 10:01:00'::timestamp - '01.01.2004 10:00:00'::timestamp)::interval;
>      ?column?
> ---------------------
> 2004-01-01 10:01:00
> (1 row)
>
> works fine in my 7.4.2

Yes. I've just tried it in PostgreSQL 7.2 and it works fine there as well.



On Jul 2 2004, Theodore Petrosky wrote:

> Interesting....
> 
> If I reverse the order it works...
> 
> agencysacks=# SELECT CAST('01.01.2004 10:00:00' AS
> TIMESTAMP) + (CAST('01.01.2004 10:01:00' AS TIMESTAMP)
> - CAST('01.01.2004 10:00:00' AS TIMESTAMP)) as answer;
>        answer        
> ---------------------
>  2004-01-01 10:01:00
> (1 row)

Yes. The above works for me as well in 7.2. 


> 
> However your original...
> 
> 
> agencysacks=# 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.
> 
> 
> agencysacks=# select version();
>                                                       
>   version                                             
>            
>  
>  
> -------------------------------------------------------------------------------------------------------------------------
>  PostgreSQL 7.4.2 on powerpc-apple-darwin7.2.0,
> compiled by GCC gcc (GCC) 3.3 20030304 (Apple
> Computer, Inc. build 1495)
> (1 row)
> 
> Looks like postgresql demands the  order to be 
> timestamp +- interval.
> 
> Ted

I am not getting the error above. I get the incorrect result as reported in 
my original report. Does this mean that this was a buggy behaviour in 7.2 
and then was changed in the latter releases to give the error message that 
7.4.2 is giving?

Thanks for the quick responses. 

Best regards,

Ilir
____________________________________________

Ilir Gashi 
PhD Student 
Centre for Software Reliability 
City University 
Northampton Square, London EC1V 0HB
email: i.gashi@city.ac.uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________

Re: timestamp arithmetic (a possible bug?)

От:

Achilleus Mantzios <achill@matrix.gatewaynet.com>

Дата:

2 июля 2004 г. в 08:37:57

O kyrios Ilir Gashi egrapse stis Jul 2, 2004 :

SELECT '01.01.2004 10:00:00'::timestamp + ('01.01.2004 
10:01:00'::timestamp - '01.01.2004 10:00:00'::timestamp)::interval;
      ?column?
---------------------
 2004-01-01 10:01:00
(1 row)

works fine in my 7.4.2

> Hi,
> 
> 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);
> 
> The result returned is: 
> 
>       ?column?
> ---------------------
>  2004-01-01 00:01:00
> (1 row)
> 
> I was expecting: 2004-01-01 10:01:00.
> 
> Tried it on Oracle 8.0.5:
> 
> SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM.YYYY HH:MI:SS') - 
> TO_DATE('01.01.2004 10:00:00', 'DD.MM.YYYY HH:MI:SS') + TO_DATE('01.01.2004 
> 10:00:00', 'DD.MM.YYYY HH:MI:SS') FROM DUAL;
> 
> --------------------------- 
> 2004-01-01 10:01:00
> (1 row  selected) 
> 
> 
> And MSSQL 7:
> 
> SELECT (CAST('01.01.2004 10:01:00' AS DATETIME) - CAST('01.01.2004 
> 10:00:00' AS DATETIME) + CAST('01.01.2004 10:00:00' AS DATETIME));
> 
> 
> --------------------------- 
> 
> 2004-01-01 10:01:00.000
> 
> (1 row(s) affected)
> 
> 
> Is this a bug? Same thing happens if I use TimestampTZ rather than 
> Timestamp.
> 
> Best regards,
> 
> Ilir
> 
> ____________________________________________
> 
> Ilir Gashi 
> PhD Student 
> Centre for Software Reliability 
> City University 
> Northampton Square, London EC1V 0HB
> email: i.gashi@city.ac.uk
> website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
> ____________________________________________
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
-Achilleus