Postgres Pro
Downloads
Home   >   mailing lists

Thread: Error in 7.2

Error in 7.2

From
rbt@barchord.com
Date:
select now() + cast('71 minutes' as interval) as case1     , cast('71 minutes' as interval) + now() as case2     ,
now()+ cast('5 minutes' as interval) as case3     , cast('5 minutes' as interval) + now() as case4     , now();
 


The above works as expected in 7.1, but in 7.2 case1 and case2 have 
different values.  Appears hours to seconds are ignored if it's 
interval + timestamp rather than timestamp + interval.

Re: Error in 7.2

From
Tom Lane
Date:
rbt@barchord.com writes:
> select now() + cast('71 minutes' as interval) as case1
>       , cast('71 minutes' as interval) + now() as case2
>       , now() + cast('5 minutes' as interval) as case3
>       , cast('5 minutes' as interval) + now() as case4
>       , now();

> The above works as expected in 7.1, but in 7.2 case1 and case2 have 
> different values.

No, it doesn't behave the way I'd expect in 7.1, either:

test71=# select version();                            version
------------------------------------------------------------------PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by
GCC2.95.3
 
(1 row)

test71=# select now() + cast('71 minutes' as interval) as case1;        case1
------------------------2002-01-16 16:44:58-05
(1 row)

test71=# select  cast('71 minutes' as interval) + now() as case2; case2
----------16:45:06
(1 row)

The operator being selected in the second case is interval_pl_time;
apparently we don't have interval + timestamp, and that's as close
as 7.1 can find.  Of course the result of now() is first converted
to time.

In current sources I get

regression=# select version();                           version
---------------------------------------------------------------PostgreSQL 7.2b5 on hppa-hp-hpux10.20, compiled by GCC
2.95.3
(1 row)

regression=# select now() + cast('71 minutes' as interval) as case1;           case1
------------------------------2002-01-16 16:46:45.37056-05
(1 row)

regression=# select  cast('71 minutes' as interval) + now() as case2;       case2
---------------------2002-01-16 01:11:00
(1 row)

The operator being selected here is timedate_pl, which seems a peculiar
choice also.  (now() is coerced to date, natch.)  I'd say this is
marginally better than 7.1's choice, but why the change in behavior?

Of course the real problem is the lack of specific interval + timestamp
addition operators.  I don't think we should hold up 7.2 release (and
force another initdb) to fix this, considering that 7.1 wasn't any
better.  Put it on TODO for 7.3, instead.

Comments?
        regards, tom lane

Re: Error in 7.2

From
"Rod Taylor"
Date:
heh.. Your right.  With the 7.1 results I didn't notice the date was
missing.
--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <rbt@barchord.com>
Cc: <pgsql-hackers@postgresql.org>; "Thomas Lockhart"
<lockhart@fourpalms.org>
Sent: Wednesday, January 16, 2002 3:44 PM
Subject: Re: [HACKERS] Error in 7.2


> rbt@barchord.com writes:
> > select now() + cast('71 minutes' as interval) as case1
> >       , cast('71 minutes' as interval) + now() as case2
> >       , now() + cast('5 minutes' as interval) as case3
> >       , cast('5 minutes' as interval) + now() as case4
> >       , now();
>
> > The above works as expected in 7.1, but in 7.2 case1 and case2
have
> > different values.
>
> No, it doesn't behave the way I'd expect in 7.1, either:
>
> test71=# select version();
>                              version
> ------------------------------------------------------------------
>  PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
>
> test71=# select now() + cast('71 minutes' as interval) as case1;
>          case1
> ------------------------
>  2002-01-16 16:44:58-05
> (1 row)
>
> test71=# select  cast('71 minutes' as interval) + now() as case2;
>   case2
> ----------
>  16:45:06
> (1 row)
>
> The operator being selected in the second case is interval_pl_time;
> apparently we don't have interval + timestamp, and that's as close
> as 7.1 can find.  Of course the result of now() is first converted
> to time.
>
> In current sources I get
>
> regression=# select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.2b5 on hppa-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
>
> regression=# select now() + cast('71 minutes' as interval) as case1;
>             case1
> ------------------------------
>  2002-01-16 16:46:45.37056-05
> (1 row)
>
> regression=# select  cast('71 minutes' as interval) + now() as
case2;
>         case2
> ---------------------
>  2002-01-16 01:11:00
> (1 row)
>
> The operator being selected here is timedate_pl, which seems a
peculiar
> choice also.  (now() is coerced to date, natch.)  I'd say this is
> marginally better than 7.1's choice, but why the change in behavior?
>
> Of course the real problem is the lack of specific interval +
timestamp
> addition operators.  I don't think we should hold up 7.2 release
(and
> force another initdb) to fix this, considering that 7.1 wasn't any
> better.  Put it on TODO for 7.3, instead.
>
> Comments?
>
> regards, tom lane
>