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

Re: BUG #1871: operations with data types

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: anris(at)polynet(dot)lviv(dot)ua, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1871: operations with data types
Date: 2005-09-11 05:33:35
Message-ID: 20050911053335.GA61855@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sun, Sep 11, 2005 at 12:43:58AM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > Apparently the two intervals don't cancel each other out (i.e.,
> > they're not optimized to zero),
> 
> Well, no, because + and - associate left-to-right.

Sure, I wasn't expecting any different -- I was just mentioning it
for the mathematically inclined who might think +1 and -1 should
cancel each other out.

> > test=> select '2005-08-31'::date + '1 month'::interval;
> >       ?column?       
> > ---------------------
> >  2005-09-30 00:00:00
> > (1 row)
> 
> This seems to be the crux of the issue: is the above expression valid
> and if so what should it yield?

Dunno.  It does seem inconsistent that these expressions give the
same answer:

select '2005-08-30'::date + '1 month'::interval,
       '2005-08-30'::date + '1 day'::interval + '1 month'::interval;
      ?column?       |      ?column?       
---------------------+---------------------
 2005-09-30 00:00:00 | 2005-09-30 00:00:00
(1 row)

and these give different answers:

select '2005-08-30'::date + '1 day'::interval + '1 month'::interval,
       '2005-08-30'::date + '1 month'::interval + '1 day'::interval;
      ?column?       |      ?column?       
---------------------+---------------------
 2005-09-30 00:00:00 | 2005-10-01 00:00:00
(1 row)

but I doubt I could make an argument for an alternative that was
any better than its counterargument.

Wait a minute, here's proof that it *must* be wrong ;-)

mysql> select date_add('2005-08-31', interval 1 month);
+------------------------------------------+
| date_add('2005-08-31', interval 1 month) |
+------------------------------------------+
| 2005-09-30                               |
+------------------------------------------+
1 row in set (0.11 sec)

What do other DBMSs do?

-- 
Michael Fuhr

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-09-11 07:03:57
Subject: Re: BUG #1871: operations with data types
Previous:From: Tom LaneDate: 2005-09-11 04:43:58
Subject: Re: BUG #1871: operations with data types

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