Subject: Re: [SQL] Interval subtracting
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Markus Schaber <schabi@logix-tt.com>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
	Stephan Szabo <sszabo@megazone.bigpanda.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, "Milen A. Radev" <milen@radev.net>,
	pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
In-Reply-To: <440EC8FF.9080201@logix-tt.com>
References: <200603012027.k21KRk423759@candle.pha.pa.us>
	<1141245396.18820.16.camel@state.g2switchworks.com>
	<440EC8FF.9080201@logix-tt.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1141832866.6249.7.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 08 Mar 2006 09:47:47 -0600

On Wed, 2006-03-08 at 06:07, Markus Schaber wrote:
> Hi, Scott,
> 
> Scott Marlowe wrote:
> 
> >>But it isn't '-2 months, -1 day'.  I think what you are saying is what I
> >>am saying, that we should make the signs consistent.
> > Pretty much.  It just seems wrong to have different signs in what is
> > essentially a single unit.
> > 
> > We don't say 42 degrees, -12 minutes when measuring arc, do we?  Then
> > again, maybe some folks do.  It just seems wrong to me.
> 
> But we say "quarter to twelve", at least in some areas on this planet.
> 
> The problem is that months have different lengths. '2 months - 1 day'
> can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1
> month 30 days', depending on the timestamp we apply the interval.

I made this point before.  In the military they say 1145 or 2345 instead
of quarter to twelve, because 1: there are two "quarter to twelves" a
day, and 2: It's easy to get it confused.  

For same reasons, i.e. a need for precision, I find it hard to accept
the idea of mixing positive and negative units in the same interval. 
The plus or minus sign should be outside of the interval.

Then, it's quite certain what you mean.  If you say 

select '2006-06-12'::date - interval '1 month 2 days' 

there is no ambiguity.  If you say:

select '2006-06-12'::date + interval '-1 month -2 days'

do you mean (1 month - 2 days) subtracted from the date, or 
do you mean to subtract 1 month, then 2 days from the date?

Putting the + or - outside the interval seems to make the most sense to
me.  Allowing them inside makes no sense to me.  And colloquialisms
aren't really a good reason.  :)