Thread: Re: [SQL] Interval subtracting

"Milen A. Radev" <milen@radev.net> writes:
> Milorad Poluga написа:
>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval
>>> ?column?        
>>> --------------- 
>>> 3 mons -14 days 
>>> 
>>> Why not '2 mons  16 days' ? 

> Please read the last paragraph in section 8.5.1.4 of the manual
> (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
> . It mentions the functions named "justify_days" and "justify_hours"
> that could do what you need.

justify_days doesn't currently do anything with this result --- it
thinks its charter is only to reduce day components that are >= 30 days.
However, I think a good case could be made that it should normalize
negative days too; that is, the invariant on its result should be
0 <= days < 30, not merely days < 30.  Similarly for justify_hours.
Comments anyone?  Patch anyone?
        regards, tom lane

On Sat, 18 Feb 2006, Tom Lane wrote:

> "Milen A. Radev" <milen@radev.net> writes:
> > Milorad Poluga написа:
> >>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval
> >>> ?column?
> >>> ---------------
> >>> 3 mons -14 days
> >>>
> >>> Why not '2 mons  16 days' ?
>
> > Please read the last paragraph in section 8.5.1.4 of the manual
> > (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
> > . It mentions the functions named "justify_days" and "justify_hours"
> > that could do what you need.
>
> justify_days doesn't currently do anything with this result --- it
> thinks its charter is only to reduce day components that are >= 30 days.
> However, I think a good case could be made that it should normalize
> negative days too; that is, the invariant on its result should be
> 0 <= days < 30, not merely days < 30.

What about cases like interval '1 month -99 days', should that turn into
interval '-3 mons +21 days' or '-2 mons -9 days'?

Tom Lane wrote:
> "Milen A. Radev" <milen@radev.net> writes:
> 
>>Milorad Poluga напи�а:
>>
>>>>SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval
>>>>?column?        
>>>>--------------- 
>>>>3 mons -14 days 
>>>>
>>>>Why not '2 mons  16 days' ? 
> 
> 
>>Please read the last paragraph in section 8.5.1.4 of the manual
>>(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
>>. It mentions the functions named "justify_days" and "justify_hours"
>>that could do what you need.
> 
> 
> justify_days doesn't currently do anything with this result --- it
> thinks its charter is only to reduce day components that are >= 30 days.
> However, I think a good case could be made that it should normalize
> negative days too; that is, the invariant on its result should be
> 0 <= days < 30, not merely days < 30.  Similarly for justify_hours.
> Comments anyone?  Patch anyone?

Sure, if nobody objects to this change I can write the patch.

mark

Mark Dilger wrote:
> Tom Lane wrote:
> > "Milen A. Radev" <milen@radev.net> writes:
> > 
> >>Milorad Poluga ????????????:
> >>
> >>>>SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 days'::interval
> >>>>?column?        
> >>>>--------------- 
> >>>>3 mons -14 days 
> >>>>
> >>>>Why not '2 mons  16 days' ? 
> > 
> > 
> >>Please read the last paragraph in section 8.5.1.4 of the manual
> >>(http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775)
> >>. It mentions the functions named "justify_days" and "justify_hours"
> >>that could do what you need.
> > 
> > 
> > justify_days doesn't currently do anything with this result --- it
> > thinks its charter is only to reduce day components that are >= 30 days.
> > However, I think a good case could be made that it should normalize
> > negative days too; that is, the invariant on its result should be
> > 0 <= days < 30, not merely days < 30.  Similarly for justify_hours.
> > Comments anyone?  Patch anyone?
> 
> Sure, if nobody objects to this change I can write the patch.

Good question.  Should we restrict days to 0 - 30 or -30 - 30?  The
current system does the later:
test=> select justify_days('-45 days');   justify_days------------------ -1 mons -15 days(1 row)
test=> select justify_days('1 month -45 days'); justify_days-------------- -15 days(1 row)
test=> select justify_days('1 month -15 days');  justify_days---------------- 1 mon -15 days(1 row)

Should we be adjusting the last one?  I am unsure.  Comments?

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Mark Dilger wrote:
> Tom Lane wrote:
> 
>> "Milen A. Radev" <milen@radev.net> writes:
>>
>>> Milorad Poluga напи�а:
>>>
>>>>> SELECT '10 years 1 mons 1 days'::interval - '9 years 10 mons 15 
>>>>> days'::interval
>>>>> ?column?        --------------- 3 mons -14 days
>>>>> Why not '2 mons  16 days' ? 
>>
>>
>>
>>> Please read the last paragraph in section 8.5.1.4 of the manual
>>> (http://www.postgresql.org/docs/8.1/static/datatype-datetime.html#AEN4775) 
>>>
>>> . It mentions the functions named "justify_days" and "justify_hours"
>>> that could do what you need.
>>
>>
>>
>> justify_days doesn't currently do anything with this result --- it
>> thinks its charter is only to reduce day components that are >= 30 days.
>> However, I think a good case could be made that it should normalize
>> negative days too; that is, the invariant on its result should be
>> 0 <= days < 30, not merely days < 30.  Similarly for justify_hours.
>> Comments anyone?  Patch anyone?
> 
> 
> Sure, if nobody objects to this change I can write the patch.
> 
> mark

I've modified the code and it now behaves as follows:
  select justify_days('3 months -12 days'::interval);    justify_days  ----------------   2 mons 18 days
  select justify_days('3 months -33 days'::interval);   justify_days  ---------------   1 mon 27 days
  select justify_hours('3 months -33 days -12 hours'::interval);         justify_hours  ---------------------------   3
mons-34 days +12:00:00
 
  select justify_days(justify_hours('3 months -33 days -12 hours'::interval));        justify_days
------------------------  1 mon 26 days 12:00:00
 
  select justify_hours('-73 hours'::interval);     justify_hours  -------------------   -4 days +23:00:00
  select justify_days('-62 days'::interval);     justify_days  ------------------   -3 mons +28 days


I find the last two results somewhat peculiar, as the new functionality pushes 
the negative values upwards (from hours to days, days to months).  Changing '-73 
hours' to '-3 days -1 hour' might be more intuitive?  The '-4 days +23 hours' is 
however consistent with the behavior in the other cases.

Thoughts?  I will package this up into a patch fairly soon.

mark

On Wed, Mar 01, 2006 at 12:59:29PM -0500, Bruce Momjian wrote:
> Good question.  Should we restrict days to 0 - 30 or -30 - 30?  The
> current system does the later:
> 
>     test=> select justify_days('-45 days');
>        justify_days
>     ------------------
>      -1 mons -15 days
>     (1 row)
> 
>     test=> select justify_days('1 month -45 days');
>      justify_days
>     --------------
>      -15 days
>     (1 row)
> 
>     test=> select justify_days('1 month -15 days');
>       justify_days
>     ----------------
>      1 mon -15 days
>     (1 row)
> 
> Should we be adjusting the last one?  I am unsure.  Comments?

ISTM it should be looking at the sign of the overall interval, and
sticking with that consistently. So while '1 mon 5 days' and '-3 mon -8
days' both make sense, '1 mon -2 days' doesn't make nearly as much sense
in the general case. Of course this is complicated by the fact that '1
mon 20 days' doesn't necessarily equate to '2 mon -10 days'... :(

One of these days we should just create a new calendar. ;)
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Mark Dilger wrote:
>    select justify_hours('-73 hours'::interval);
>       justify_hours
>    -------------------
>     -4 days +23:00:00
> 
>    select justify_days('-62 days'::interval);
>       justify_days
>    ------------------
>     -3 mons +28 days
> 
> 
> I find the last two results somewhat peculiar, as the new functionality
> pushes the negative values upwards (from hours to days, days to months).
> Changing '-73 hours' to '-3 days -1 hour' might be more intuitive?
> The '-4 days +23 hours' is however consistent with the behavior in the
> other cases.

I don't think we can accept a change that takes a negative and turns it
into a positive and negative.  I think the answer to the last one should
be '-2 mons -2 days', which is what it does now:test=> select justify_days('-62 days'::interval);
justify_days------------------2 mons -2 days(1 row)
 

The open question is whether we should convert a positive and negative
to a positive, or a negative, based on the sign of the highest value,
e.g. convert '1 mons -10 days' to '20 days', and '-1 mons 10 days' to
'-20 days'?

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Stephan Szabo wrote:
> > justify_days doesn't currently do anything with this result --- it
> > thinks its charter is only to reduce day components that are >= 30 days.
> > However, I think a good case could be made that it should normalize
> > negative days too; that is, the invariant on its result should be
> > 0 <= days < 30, not merely days < 30.
> 
> What about cases like interval '1 month -99 days', should that turn into
> interval '-3 mons +21 days' or '-2 mons -9 days'?

I think it should be the later.  It is best to have a single sign, and I
think it is possible in all cases:
'2 mons -1 days'

could be adjusted to '1 mons 29 days'.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
> Stephan Szabo wrote:
> > > justify_days doesn't currently do anything with this result --- it
> > > thinks its charter is only to reduce day components that are >= 30 days.
> > > However, I think a good case could be made that it should normalize
> > > negative days too; that is, the invariant on its result should be
> > > 0 <= days < 30, not merely days < 30.
> > 
> > What about cases like interval '1 month -99 days', should that turn into
> > interval '-3 mons +21 days' or '-2 mons -9 days'?
> 
> I think it should be the later.  It is best to have a single sign, and I
> think it is possible in all cases:
> 
>     '2 mons -1 days'
> 
> could be adjusted to '1 mons 29 days'.

There's a part of me that thinks the WHOLE THING should be positive or
negative:

-(2 months 1 day)

Scott Marlowe wrote:
> On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
> > Stephan Szabo wrote:
> > > > justify_days doesn't currently do anything with this result --- it
> > > > thinks its charter is only to reduce day components that are >= 30 days.
> > > > However, I think a good case could be made that it should normalize
> > > > negative days too; that is, the invariant on its result should be
> > > > 0 <= days < 30, not merely days < 30.
> > > 
> > > What about cases like interval '1 month -99 days', should that turn into
> > > interval '-3 mons +21 days' or '-2 mons -9 days'?
> > 
> > I think it should be the later.  It is best to have a single sign, and I
> > think it is possible in all cases:
> > 
> >     '2 mons -1 days'
> > 
> > could be adjusted to '1 mons 29 days'.
> 
> There's a part of me that thinks the WHOLE THING should be positive or
> negative:
> 
> -(2 months 1 day)

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.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't think we can accept a change that takes a negative and turns it
> into a positive and negative.

Yeah, I find the patch's changes to the regression results pretty
disturbing.

Perhaps the correct definition ought to be like "if month part >= 0
then the reduced day part should be between 0 and 30, otherwise the
reduced day part should be between 0 and -30".  However there are
still corner cases to worry about.  If the original month and day
parts are of different sign, you might not be able to do such a
reduction without changing the sign of the month part, consider
"1 month -95 days".  Not clear what to do with this.

I guess I would expect a good result to satisfy one of these three
cases:* month > 0 and 0 <= day < 30* month < 0 and -30 < day <= 0* month = 0 and -30 < day < 30
If you believe that then "1 month -95 days" should justify to
"-2 months -5 days".
        regards, tom lane

Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
> Stephan Szabo wrote:
> > > justify_days doesn't currently do anything with this result --- it
> > > thinks its charter is only to reduce day components that are >= 30 days.
> > > However, I think a good case could be made that it should normalize
> > > negative days too; that is, the invariant on its result should be
> > > 0 <= days < 30, not merely days < 30.
> > 
> > What about cases like interval '1 month -99 days', should that turn into
> > interval '-3 mons +21 days' or '-2 mons -9 days'?
> 
> I think it should be the later.  It is best to have a single sign, and I
> think it is possible in all cases:
> 
>     '2 mons -1 days'
> 
> could be adjusted to '1 mons 29 days'.

But unfortunately '2 mons -1 days' <> '1 mons 29 days'

If I want something to happen 1 day less than two months from dome date,
then the only way to say that consistently *is* '2 mons -1 days'.

--------------
Hannu

On Wed, 1 Mar 2006, Hannu Krosing wrote:

> Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
> > Stephan Szabo wrote:
> > > > justify_days doesn't currently do anything with this result --- it
> > > > thinks its charter is only to reduce day components that are >= 30 days.
> > > > However, I think a good case could be made that it should normalize
> > > > negative days too; that is, the invariant on its result should be
> > > > 0 <= days < 30, not merely days < 30.
> > >
> > > What about cases like interval '1 month -99 days', should that turn into
> > > interval '-3 mons +21 days' or '-2 mons -9 days'?
> >
> > I think it should be the later.  It is best to have a single sign, and I
> > think it is possible in all cases:
> >
> >     '2 mons -1 days'
> >
> > could be adjusted to '1 mons 29 days'.
>
> But unfortunately '2 mons -1 days' <> '1 mons 29 days'
>
> If I want something to happen 1 day less than two months from dome date,
> then the only way to say that consistently *is* '2 mons -1 days'.

Right, but would you call justify_days on such an interval?  '2 months -1
days' <> '1 mon 29 days', but '1 mon 60 days' is also <> '3 mons' in
general usage.

Hannu Krosing wrote:
> ?hel kenal p?eval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian:
> > Stephan Szabo wrote:
> > > > justify_days doesn't currently do anything with this result --- it
> > > > thinks its charter is only to reduce day components that are >= 30 days.
> > > > However, I think a good case could be made that it should normalize
> > > > negative days too; that is, the invariant on its result should be
> > > > 0 <= days < 30, not merely days < 30.
> > > 
> > > What about cases like interval '1 month -99 days', should that turn into
> > > interval '-3 mons +21 days' or '-2 mons -9 days'?
> > 
> > I think it should be the later.  It is best to have a single sign, and I
> > think it is possible in all cases:
> > 
> >     '2 mons -1 days'
> > 
> > could be adjusted to '1 mons 29 days'.
> 
> But unfortunately '2 mons -1 days' <> '1 mons 29 days'
> 
> If I want something to happen 1 day less than two months from dome date,
> then the only way to say that consistently *is* '2 mons -1 days'.

Right, but you asked to justify the days by calling the function.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I don't think we can accept a change that takes a negative and turns it
> > into a positive and negative.
> 
> Yeah, I find the patch's changes to the regression results pretty
> disturbing.
> 
> Perhaps the correct definition ought to be like "if month part >= 0
> then the reduced day part should be between 0 and 30, otherwise the
> reduced day part should be between 0 and -30".  However there are
> still corner cases to worry about.  If the original month and day
> parts are of different sign, you might not be able to do such a
> reduction without changing the sign of the month part, consider
> "1 month -95 days".  Not clear what to do with this.
> 
> I guess I would expect a good result to satisfy one of these three
> cases:
>     * month > 0 and 0 <= day < 30
>     * month < 0 and -30 < day <= 0
>     * month = 0 and -30 < day < 30
> If you believe that then "1 month -95 days" should justify to
> "-2 months -5 days".

I believe it.  :-)

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Hannu Krosing <hannu@skype.net> writes:
> But unfortunately '2 mons -1 days' <> '1 mons 29 days'
> If I want something to happen 1 day less than two months from dome date,
> then the only way to say that consistently *is* '2 mons -1 days'.

Sure, but if you want to represent that then you don't pass the value
through justify_days().  The entire premise of justify_days() is that
1 month is interchangeable with 30 days and we should try to make the
value "look nice" given that assumption.

I think everyone's independently arrived at the same thought that
justify_days should not produce a result with different signs for month
and day (except for the case with month = 0, per my last message).
        regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>I don't think we can accept a change that takes a negative and turns it
>>into a positive and negative.
> 
> 
> Yeah, I find the patch's changes to the regression results pretty
> disturbing.
> 
> Perhaps the correct definition ought to be like "if month part >= 0
> then the reduced day part should be between 0 and 30, otherwise the
> reduced day part should be between 0 and -30".  However there are
> still corner cases to worry about.  If the original month and day
> parts are of different sign, you might not be able to do such a
> reduction without changing the sign of the month part, consider
> "1 month -95 days".  Not clear what to do with this.
> 
> I guess I would expect a good result to satisfy one of these three
> cases:
>     * month > 0 and 0 <= day < 30
>     * month < 0 and -30 < day <= 0
>     * month = 0 and -30 < day < 30
> If you believe that then "1 month -95 days" should justify to
> "-2 months -5 days".
> 
>             regards, tom lane

How would you expect justify_hours to behave?  I extrapolate from your rules 
above that:
      * month > 0 and 0 <= day < 30 and 0 <= hours < 24      * month < 0 and -30 < day <= 0 and -24 < hours <= 0      *
month= 0 and -30 < day <= 0 and -24 < hours <= 0      * month = 0 and 0 <= day < 30 and 0 <= hours < 24
 

Which would mean that '1 month -95 days -12 hours' should justify to "-2 months 
-5 days -12 hours" rather than "-2 months -6 days 12 hours", but that '1 month 
-15 days -12 hours" would justify to '14 days 12 hours' rather than '15 days -12 
hours'.

Is this correct?

mark

Hannu Krosing wrote:
> But unfortunately '2 mons -1 days' <> '1 mons 29 days'
> 
> If I want something to happen 1 day less than two months from dome date,
> then the only way to say that consistently *is* '2 mons -1 days'.

Correct me if I am wrong, but I thought that justify_days would only be called 
if the user wanted it.  I get the following behavior in psql even after the 
patch is applied:

select '2 mons -1 days'::interval;    interval
---------------- 2 mons -1 days


So there does not seem to be any justification going on without the user's 
permission.  Consequently, if you need '2 mons -1 days', don't call justify_days.

Am I missing something?

mark

On Wed, 2006-03-01 at 14:27, Bruce Momjian wrote:
> Scott Marlowe wrote:
> > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote:
> > > Stephan Szabo wrote:
> > > > > justify_days doesn't currently do anything with this result --- it
> > > > > thinks its charter is only to reduce day components that are >= 30 days.
> > > > > However, I think a good case could be made that it should normalize
> > > > > negative days too; that is, the invariant on its result should be
> > > > > 0 <= days < 30, not merely days < 30.
> > > > 
> > > > What about cases like interval '1 month -99 days', should that turn into
> > > > interval '-3 mons +21 days' or '-2 mons -9 days'?
> > > 
> > > I think it should be the later.  It is best to have a single sign, and I
> > > think it is possible in all cases:
> > > 
> > >     '2 mons -1 days'
> > > 
> > > could be adjusted to '1 mons 29 days'.
> > 
> > There's a part of me that thinks the WHOLE THING should be positive or
> > negative:
> > 
> > -(2 months 1 day)
> 
> 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.

Mark Dilger <pgsql@markdilger.com> writes:
> Tom Lane wrote:
>> I guess I would expect a good result to satisfy one of these three
>> cases:
>>    * month > 0 and 0 <= day < 30
>>    * month < 0 and -30 < day <= 0
>>    * month = 0 and -30 < day < 30
>> If you believe that then "1 month -95 days" should justify to
>> "-2 months -5 days".

> How would you expect justify_hours to behave?  I extrapolate from your rules 
> above that:

>        * month > 0 and 0 <= day < 30 and 0 <= hours < 24
>        * month < 0 and -30 < day <= 0 and -24 < hours <= 0
>        * month = 0 and -30 < day <= 0 and -24 < hours <= 0
>        * month = 0 and 0 <= day < 30 and 0 <= hours < 24

Hmmm ... I think it would be better if the two functions were
independent, if possible.  Your spec above implies that justify_hours
implicitly does justify_days as well, which seems a bit restrictive.

Furthermore, justify_hours should only assume that 1 day == 24 hours,
which while broken by DST is still a lot solider assumption than
justify_days' 1 month == 30 days.  I can well believe that a lot of
people only want to make the first assumption.

So I'm inclined to think that justify_hours is responsible for reducing
the seconds part to less-than-24-hours and pushing any overflow into the
days part (but not touching months), while justify_days is responsible
for reducing the days part to less-than-30-days and pushing any overflow
into the months part (but not touching seconds).  If you want both you
apply both functions, probably in that order.  (I wonder if there are
any cases where applying justify_days before justify_hours would be
useful.  Offhand I can't see one ...)
        regards, tom lane

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> writes:
> 
>>Tom Lane wrote:
>>
>>>I guess I would expect a good result to satisfy one of these three
>>>cases:
>>>    * month > 0 and 0 <= day < 30
>>>    * month < 0 and -30 < day <= 0
>>>    * month = 0 and -30 < day < 30
>>>If you believe that then "1 month -95 days" should justify to
>>>"-2 months -5 days".
> 
> 
>>How would you expect justify_hours to behave?  I extrapolate from your rules 
>>above that:
> 
> 
>>       * month > 0 and 0 <= day < 30 and 0 <= hours < 24
>>       * month < 0 and -30 < day <= 0 and -24 < hours <= 0
>>       * month = 0 and -30 < day <= 0 and -24 < hours <= 0
>>       * month = 0 and 0 <= day < 30 and 0 <= hours < 24
> 
> 
> Hmmm ... I think it would be better if the two functions were
> independent, if possible.  Your spec above implies that justify_hours
> implicitly does justify_days as well, which seems a bit restrictive.
> 
> Furthermore, justify_hours should only assume that 1 day == 24 hours,
> which while broken by DST is still a lot solider assumption than
> justify_days' 1 month == 30 days.  I can well believe that a lot of
> people only want to make the first assumption.
> 
> So I'm inclined to think that justify_hours is responsible for reducing
> the seconds part to less-than-24-hours and pushing any overflow into the
> days part (but not touching months), while justify_days is responsible
> for reducing the days part to less-than-30-days and pushing any overflow
> into the months part (but not touching seconds).  If you want both you
> apply both functions, probably in that order.  (I wonder if there are
> any cases where applying justify_days before justify_hours would be
> useful.  Offhand I can't see one ...)
> 
>             regards, tom lane

I did not mean to imply that the two functions would be calling each other. 
Rather, I thought that a user should get sensible results if they called them 
both together.  The current code (without the patch) behaves as follows:
  select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));        justify_days
-------------------------  4 mons 4 days -12:00:00
 

which seems inconsistent with the intent of the patch.  Shouldn't the patched 
version return '4 mons 3 days 12:00:00' instead?

mark

Mark Dilger <pgsql@markdilger.com> writes:
> The current code (without the patch) behaves as follows:

>    select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
>          justify_days
>    -------------------------
>     4 mons 4 days -12:00:00

So?  If we liked the current behavior we wouldn't be discussing a patch...

My thought is that justify_hours should reduce that input to
'1 month 93 days 12:00:00' and then justify_days would produce
'4 months 3 days 12:00:00'.
        regards, tom lane

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> writes:
> 
>>The current code (without the patch) behaves as follows:
> 
> 
>>   select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
>>         justify_days
>>   -------------------------
>>    4 mons 4 days -12:00:00
> 
> 
> So?  If we liked the current behavior we wouldn't be discussing a patch...
> 
> My thought is that justify_hours should reduce that input to
> '1 month 93 days 12:00:00' and then justify_days would produce
> '4 months 3 days 12:00:00'.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

Your proposal is that justify_hours borrows 24 hours from the days column in 
order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
if the days column is a positive number?  What if it is negative?

I think we all agree on the following but nobody is explicitly saying so:
  select justify_days(justify_hours('2 days -12:00:00'::interval))        justify_days  -------------------------   1
day12:00:00
 
  select justify_days(justify_hours('-2 days -12:00:00'::interval))        justify_days  -------------------------   -2
days-12:00:00
 

Am I correct that the second case should still have negative hours?  If so, then 
justify_hours(...) needs to examine the sign of the days and months portion of 
the interval while performing its work.

mark

Mark Dilger wrote:
> Your proposal is that justify_hours borrows 24 hours from the days column in 
> order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
> if the days column is a positive number?  What if it is negative?
> 
> I think we all agree on the following but nobody is explicitly saying so:
> 
>    select justify_days(justify_hours('2 days -12:00:00'::interval))
>          justify_days
>    -------------------------
>     1 day 12:00:00

Right.

>    select justify_days(justify_hours('-2 days -12:00:00'::interval))
>          justify_days
>    -------------------------
>     -2 days -12:00:00

Right, unchanged.

> Am I correct that the second case should still have negative hours?  If so, then 
> justify_hours(...) needs to examine the sign of the days and months portion of 
> the interval while performing its work.

Yes, it would need to look at both, and this opens a new problem. 
Imagine this:
'1 mons -2 days -12:00:00'

Which sign do we head to for this?  For justify_hours, if we don't look
at the months it remains unchange, but calling justify_days we get:
'28 days -12:00:00'

which is wrong (negative and positive).  Now if we knew justify_days was
going to be called we would have had justify_hours return '-3 days
12:00:00' so the final result after calling justify_days would be '27
days 12:00:00'.

My head hurts.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Mark Dilger wrote:
> 
>>Your proposal is that justify_hours borrows 24 hours from the days column in 
>>order to bring the -12 hours up to a positive 12 hours.  Should it only do that 
>>if the days column is a positive number?  What if it is negative?
>>
>>I think we all agree on the following but nobody is explicitly saying so:
>>
>>   select justify_days(justify_hours('2 days -12:00:00'::interval))
>>         justify_days
>>   -------------------------
>>    1 day 12:00:00
> 
> 
> Right.
> 
> 
>>   select justify_days(justify_hours('-2 days -12:00:00'::interval))
>>         justify_days
>>   -------------------------
>>    -2 days -12:00:00
> 
> 
> Right, unchanged.
> 
> 
>>Am I correct that the second case should still have negative hours?  If so, then 
>>justify_hours(...) needs to examine the sign of the days and months portion of 
>>the interval while performing its work.
> 
> 
> Yes, it would need to look at both, and this opens a new problem. 
> Imagine this:
> 
>     '1 mons -2 days -12:00:00'
> 
> Which sign do we head to for this?  For justify_hours, if we don't look
> at the months it remains unchange, but calling justify_days we get:
> 
>     '28 days -12:00:00'
> 
> which is wrong (negative and positive).  Now if we knew justify_days was
> going to be called we would have had justify_hours return '-3 days
> 12:00:00' so the final result after calling justify_days would be '27
> days 12:00:00'.
> 
> My head hurts.
> 

I am just now testing a patch which handles all of this.  justify_hours *makes 
no change to months or days*, but it examines them both to determine if the 
total amount of time represented there is positive or negative.  It then makes 
sure that the hours have the same sign.

Of course, if you never get around to calling justify_days, you'll have mixed 
signs in your results.  But if days and months have different signs to begin 
with, then that isn't the fault of justify_hours, so we really haven't done any 
harm.

I'll be posting the patch shortly.

mark

A new patch is attached.  Please note the regression differences.

mark



Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context=5 -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c    22 Nov 2005 22:30:33 -0000    1.160
--- src/backend/utils/adt/timestamp.c    1 Mar 2006 22:29:32 -0000
***************
*** 2003,2013 ****
      TMODULO(result->time, wholeday, USECS_PER_DAY);
  #else
      TMODULO(result->time, wholeday, (double) SECS_PER_DAY);
  #endif
      result->day += wholeday;    /* could overflow... */
!
      PG_RETURN_INTERVAL_P(result);
  }

  /*
   *    interval_justify_days()
--- 2003,2024 ----
      TMODULO(result->time, wholeday, USECS_PER_DAY);
  #else
      TMODULO(result->time, wholeday, (double) SECS_PER_DAY);
  #endif
      result->day += wholeday;    /* could overflow... */
!     if ((result->time < 0) &&
!          ((result->month >= 0 && result->day >= 0) ||
!           (result->month > 0 && result->day < 0 && (-1.0 * (double)result->day)/((double)result->month) <
((double)DAYS_PER_MONTH))|| 
!           (result->month < 0 && result->day > 0 && ((double)result->day)/(-1.0 * (double)result->month) >
((double)DAYS_PER_MONTH))))
!     {
! #ifdef HAVE_INT64_TIMESTAMP
!         result->time += USECS_PER_DAY;
! #else
!         result->time += (double) SECS_PER_DAY;
! #endif
!         result->day--;
!     }
      PG_RETURN_INTERVAL_P(result);
  }

  /*
   *    interval_justify_days()
***************
*** 2028,2037 ****
--- 2039,2053 ----
      result->time = span->time;

      wholemonth = result->day / DAYS_PER_MONTH;
      result->day -= wholemonth * DAYS_PER_MONTH;
      result->month += wholemonth;
+     if (result->day < 0 && result->month > 0)
+     {
+         result->day += DAYS_PER_MONTH;
+         result->month--;
+     }

      PG_RETURN_INTERVAL_P(result);
  }

  /* timestamp_pl_interval()
*** ./expected/timestamp.out    Sat Jun 25 20:04:18 2005
--- ./results/timestamp.out    Wed Mar  1 14:26:33 2006
***************
*** 488,494 ****
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 6 hours 27 mins 59 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
--- 488,494 ----
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 1 day -17 hours -32 mins -1 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
***************
*** 557,563 ****
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 6 hours 27 mins 59 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
--- 557,563 ----
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 1 day -17 hours -32 mins -1 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec

======================================================================

*** ./expected/timestamptz.out    Sat Jun 25 20:04:18 2005
--- ./results/timestamptz.out    Wed Mar  1 14:26:34 2006
***************
*** 483,489 ****
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 6 hours 27 mins 59 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
--- 483,489 ----
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 1 day -17 hours -32 mins -1 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
***************
*** 551,557 ****
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 6 hours 27 mins 59 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec
--- 551,557 ----
      | @ 306 days 6 hours 27 mins 59 secs ago
      | @ 2 days 6 hours 27 mins 59 secs ago
      | @ 1 day 6 hours 27 mins 59 secs ago
!     | @ 1 day -17 hours -32 mins -1 secs ago
      | @ 57 days 17 hours 32 mins 1 sec
      | @ 58 days 17 hours 32 mins 1 sec
      | @ 362 days 17 hours 32 mins 1 sec

======================================================================

*** ./expected/horology.out    Thu Sep  8 09:49:04 2005
--- ./results/horology.out    Wed Mar  1 14:26:36 2006
***************
*** 2115,2124 ****
       | Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
       | Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
       | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 59 mins 59 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 5 hours 59 mins 56 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 9 hours 59 mins 58 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 10 hours 59 mins 57 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
--- 2115,2124 ----
       | Wed Mar 15 02:14:05 2000 PST | Fri Dec 31 17:32:01 1999 PST | @ 74 days 8 hours 42 mins 4 secs
       | Wed Mar 15 02:14:05 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 8 hours 42 mins 4 secs
       | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 0
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 1 day -23 hours -1 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -18 hours -4 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -14 hours -2 secs ago
!      | Wed Mar 15 02:14:05 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -13 hours -3 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 15 hours 17 mins 56 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 15 hours 17 mins 56 secs ago
       | Wed Mar 15 02:14:05 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 15 hours 5 mins 15 secs ago
***************
*** 2132,2140 ****
       | Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
       | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
       | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 59 mins 57 secs ago
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 8 hours 59 mins 59 secs ago
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 9 hours 59 mins 58 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
--- 2132,2140 ----
       | Wed Mar 15 03:14:04 2000 PST | Sat Jan 01 17:32:01 2000 PST | @ 73 days 9 hours 42 mins 3 secs
       | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 59 mins 59 secs
       | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 0
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 1 day -19 hours -3 secs ago
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -15 hours -1 secs ago
!      | Wed Mar 15 03:14:04 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -14 hours -2 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 14 hours 17 mins 57 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 14 hours 17 mins 57 secs ago
       | Wed Mar 15 03:14:04 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 14 hours 5 mins 16 secs ago
***************
*** 2149,2156 ****
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
!      | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 4 hours 2 secs ago
!      | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 5 hours 1 sec ago
       | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
       | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
       | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
--- 2149,2156 ----
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 02:14:05 2000 PST | @ 5 hours 59 mins 56 secs
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 4 hours 59 mins 57 secs
       | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 0
!      | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 1 day -19 hours -59 mins -58 secs ago
!      | Wed Mar 15 08:14:01 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -18 hours -59 mins -59 secs ago
       | Wed Mar 15 08:14:01 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 9 hours 18 mins ago
       | Wed Mar 15 08:14:01 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 9 hours 18 mins ago
       | Wed Mar 15 08:14:01 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 9 hours 5 mins 19 secs ago
***************
*** 2166,2172 ****
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
!      | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 59 mins 59 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago
--- 2166,2172 ----
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 03:14:04 2000 PST | @ 8 hours 59 mins 59 secs
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 08:14:01 2000 PST | @ 4 hours 2 secs
       | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 12:14:03 2000 PST | @ 0
!      | Wed Mar 15 12:14:03 2000 PST | Wed Mar 15 13:14:02 2000 PST | @ 1 day -23 hours -1 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Sun Dec 31 17:32:01 2000 PST | @ 291 days 5 hours 17 mins 58 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Mon Jan 01 17:32:01 2001 PST | @ 292 days 5 hours 17 mins 58 secs ago
       | Wed Mar 15 12:14:03 2000 PST | Sat Sep 22 18:19:20 2001 PDT | @ 556 days 5 hours 5 mins 17 secs ago

======================================================================

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Imagine this:

>     '1 mons -2 days -12:00:00'

> Which sign do we head to for this?  For justify_hours, if we don't look
> at the months it remains unchange, but calling justify_days we get:

>     '28 days -12:00:00'

> which is wrong (negative and positive).

Ugh, that's not good.

Based on that, I guess I have to change my vote: justify_hours should
still not look at the month (because it shouldn't use the month=30days
assumption), but justify_days should be changed to be effectively a
combination of both functions --- that is, it should fix all three
fields using both the 30days and the 24hours assumptions.  Then it could
guarantee that all come out with the same sign.
        regards, tom lane

Tom Lane wrote:> Mark Dilger <pgsql@markdilger.com> writes:>>>Am I correct that the second case should still have
negativehours?>>> Yes...>>>>If so, then justify_hours(...) needs to examine the sign of the days>>and months portion of
theinterval while performing its work.>>> No, it should ignore the months part completely, IMHO.  You are just>
confusingmatters by using both functions in your examples, as then> it's not clear which does what.>>
regards,tom lane
 

I like the idea that a person has some justify-path by which they can get all 
the signs to match.  With the patch that I just posted, this is accomplished as 
follows:
  justify_days(justify_hours(...))

Regardless of the particular weirdness of the signs in the original interval. 
But the patch also leaves open the possibility that you don't want the hours 
touched, perhaps because you're dealing with a daylight savings time period and 
can't accept the concept of a 24-hour day.  In that case:
  justify_days(...)

will get the sign on the months and days to match each other, though perhaps not 
match the hours.  In the event that you want to justify the hours, but can't 
accept having the days justified (because you have a non-30 day month), then you 
can call:
  justify_hours(...)

and get the sign on the hours portion to match the overall intent of the 
interval (positive or negative) without being forced to actually change the way 
the days and months are being represented.

This overall design seems more flexible than Tom's recent post in which he 
stated that justify_days should call justify_hours internally.  I tend not to 
agree.  However, it wouldn't hurt to have a justify_interval(...) function which 
does justify both in one shot.

mark

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Imagine this:
> 
> >     '1 mons -2 days -12:00:00'
> 
> > Which sign do we head to for this?  For justify_hours, if we don't look
> > at the months it remains unchange, but calling justify_days we get:
> 
> >     '28 days -12:00:00'
> 
> > which is wrong (negative and positive).
> 
> Ugh, that's not good.
> 
> Based on that, I guess I have to change my vote: justify_hours should
> still not look at the month (because it shouldn't use the month=30days
> assumption), but justify_days should be changed to be effectively a
> combination of both functions --- that is, it should fix all three
> fields using both the 30days and the 24hours assumptions.  Then it could
> guarantee that all come out with the same sign.

If we do that, we should just call it justify_interval().  I am thinking
this is the direction to go, and for people who want more control they
use the justify_hours and justify_days, and those are left unchanged.

Should justify_days() look at hours only if the day and hours signs
differ?  And perhaps only if the hours is between -24 and 0.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> If we do that, we should just call it justify_interval().  I am thinking
> this is the direction to go, and for people who want more control they
> use the justify_hours and justify_days, and those are left unchanged.

I agree.  Let's leave the existing functions alone.  I can roll-up the changes 
made so far into a new function as Bruce suggests.

mark

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Based on that, I guess I have to change my vote: justify_hours should
>> still not look at the month (because it shouldn't use the month=30days
>> assumption), but justify_days should be changed to be effectively a
>> combination of both functions --- that is, it should fix all three
>> fields using both the 30days and the 24hours assumptions.  Then it could
>> guarantee that all come out with the same sign.

> If we do that, we should just call it justify_interval().  I am thinking
> this is the direction to go, and for people who want more control they
> use the justify_hours and justify_days, and those are left unchanged.

Well, the question is whether justify_days has a sane definition that is
different from this.  Based on your example, I'm not seeing one.
        regards, tom lane

Tom Lane wrote:
> Well, the question is whether justify_days has a sane definition that is
> different from this.  Based on your example, I'm not seeing one.

Backwards compatibility is probably more important than sanity.  Let's just 
deprecate the existing functions and recommend that people use 
justify_interval(...).  By not changing the existing functions we can avoid a 
certain amount of hell.

mark

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Based on that, I guess I have to change my vote: justify_hours should
> >> still not look at the month (because it shouldn't use the month=30days
> >> assumption), but justify_days should be changed to be effectively a
> >> combination of both functions --- that is, it should fix all three
> >> fields using both the 30days and the 24hours assumptions.  Then it could
> >> guarantee that all come out with the same sign.
> 
> > If we do that, we should just call it justify_interval().  I am thinking
> > this is the direction to go, and for people who want more control they
> > use the justify_hours and justify_days, and those are left unchanged.
> 
> Well, the question is whether justify_days has a sane definition that is
> different from this.  Based on your example, I'm not seeing one.

Uh, justify days only deals with days <--> months conversions.  There is
no processing for hours.  I don't understand your comment.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Well, the question is whether justify_days has a sane definition that is
>> different from this.  Based on your example, I'm not seeing one.

> Uh, justify days only deals with days <--> months conversions.  There is
> no processing for hours.  I don't understand your comment.

So it won't guarantee that hours has a consistent sign.  If you're OK
with that, then that's fine, let's make justify_days work that way and
then provide a justify_interval that processes all three fields.
        regards, tom lane

Mark Dilger <pgsql@markdilger.com> writes:
> Bruce Momjian wrote:
>> If we do that, we should just call it justify_interval().  I am thinking
>> this is the direction to go, and for people who want more control they
>> use the justify_hours and justify_days, and those are left unchanged.

> I agree.  Let's leave the existing functions alone.

No, we still need to fix them to not leave a large negative value in
place for seconds or days (respectively).  The current coding is
unquestionably inadequate.
        regards, tom lane

Mark Dilger <pgsql@markdilger.com> writes:
> This overall design seems more flexible than Tom's recent post in which he 
> stated that justify_days should call justify_hours internally.

AFAIR I said the exact opposite.
        regards, tom lane

Mark Dilger wrote:
> Tom Lane wrote:
> > Well, the question is whether justify_days has a sane definition that is
> > different from this.  Based on your example, I'm not seeing one.
> 
> Backwards compatibility is probably more important than sanity.  Let's just 
> deprecate the existing functions and recommend that people use 
> justify_interval(...).  By not changing the existing functions we can avoid a 
> certain amount of hell.

Those functions are new in 8.1 so I do think we can improve them in 8.2
if we agree.  Tom's idea of:

>       * month > 0 and 0 <= day < 30
>       * month < 0 and -30 < day <= 0
>       * month = 0 and -30 < day < 30

seems a good change for 8.2, and the same for justify_hours(). The
question is whether justify_days should also adjust hours I think is the
issue, and the reason for a justify_interval() function.  Even if we had
people do:
justify_hours(justify_days(justify_hours()))

I don't think that would do what we want in all cases.  Consider '1 mon
-1 hour'.  That should be '29 days 23 hours' but neither existing
function, even if modified, will allow us to return that.  Only
something like justify_interval() could do it.

--  Bruce Momjian   http://candle.pha.pa.us SRA OSS, Inc.   http://www.sraoss.com
 + If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> writes:
> 
>>This overall design seems more flexible than Tom's recent post in which he 
>>stated that justify_days should call justify_hours internally.
> 
> 
> AFAIR I said the exact opposite.
> 
>             regards, tom lane

Tom Lane also wrote:
> assumption), but justify_days should be changed to be effectively a
> combination of both functions --- that is, it should fix all three
> fields using both the 30days and the 24hours assumptions.  Then it could
> guarantee that all come out with the same sign.


How is changing justify days so that it touches the hours field different from 
having justify_days call justify_hours?

mark

Bruce Momjian wrote:
> Even if we had people do:
> 
>     justify_hours(justify_days(justify_hours()))
> 
> I don't think that would do what we want in all cases.  Consider '1 mon
> -1 hour'.  That should be '29 days 23 hours' but neither existing
> function, even if modified, will allow us to return that.  Only
> something like justify_interval() could do it.
> 

justify_days(justify_hours(...)) fixes *everything* in the most recently 
submitted patch, regardless of the convoluted case you invent.  There is no data 
for which it won't work.  There is no need for justify_interval(...), except as 
syntactic sugar.

Since the backward compatibility argument didn't convince you, then we should go 
with the existing patch as-is.  Whether we introduce the new function 
justify_interval(...) could be treated as a separate question, though I don't 
mind putting that in the patch and resubmitting.

mark

Mark Dilger <pgsql@markdilger.com> writes:
> justify_days(justify_hours(...)) fixes *everything* in the most recently 
> submitted patch, regardless of the convoluted case you invent.  There is no data 
> for which it won't work.

If so, one function or the other is cheating.  Per discussion,
justify_hours must never touch months, and I don't believe that
justify_days should touch seconds either.  The proposed justify_interval
function should have a result different from successive application
of the two existing functions, because it will ensure that all three
fields have similar signs whereas separate use of the two functions
can't promise that in corner cases.
        regards, tom lane

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> writes:
> 
>>justify_days(justify_hours(...)) fixes *everything* in the most recently 
>>submitted patch, regardless of the convoluted case you invent.  There is no data 
>>for which it won't work.
> 
> 
> If so, one function or the other is cheating.  Per discussion,
> justify_hours must never touch months, and I don't believe that
> justify_days should touch seconds either.  The proposed justify_interval
> function should have a result different from successive application
> of the two existing functions, because it will ensure that all three
> fields have similar signs whereas separate use of the two functions
> can't promise that in corner cases.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

That depends what you mean by cheating.  The justify_hours function looks to see 
what answer justify_days would give, but does not actually change the data.  I 
described this all earlier and I still don't see why there is anything wrong 
with it.

mark

Mark Dilger <pgsql@markdilger.com> writes:
> Tom Lane wrote:
>> If so, one function or the other is cheating.

> That depends what you mean by cheating.  The justify_hours function
> looks to see what answer justify_days would give, but does not
> actually change the data.  I described this all earlier and I still
> don't see why there is anything wrong with it.

The problem is that you can't determine "what answer justify_days would
give" without using the assumption "1 month == 30 days", which is an
assumption that justify_hours must not depend on.
        regards, tom lane

Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.com> writes:
> 
>>Tom Lane wrote:
>>
>>>If so, one function or the other is cheating.
> 
> 
>>That depends what you mean by cheating.  The justify_hours function
>>looks to see what answer justify_days would give, but does not
>>actually change the data.  I described this all earlier and I still
>>don't see why there is anything wrong with it.
> 
> 
> The problem is that you can't determine "what answer justify_days would
> give" without using the assumption "1 month == 30 days", which is an
> assumption that justify_hours must not depend on.

Ahhh.  So the fact that justify_days already makes the 1 month == 30 days 
assumption is ok in that function but can't be propagated to justify_hours.

Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:

> > 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 do say both "quarter past three" (3 hours 15 min) and "quarter to
four" (4 hours -15 min) when talking about time.

---------------
Hannu

Hannu Krosing schrieb:
> Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:
> 
...
> But we do say both "quarter past three" (3 hours 15 min) and "quarter to
> four" (4 hours -15 min) when talking about time.
> 
but luckily we dont write it ;)
Some people say (like ) this: quarter past 3, half past 3, three quartespast 3, 4. Which seems more logical. :-)

But "saying" would be a job for to_char, not for internal storage,
which should _always_ be canonical.

Regards
Tino

Mark Dilger <pgsql@markdilger.com> writes:
> Tom Lane wrote:
>> The problem is that you can't determine "what answer justify_days would
>> give" without using the assumption "1 month == 30 days", which is an
>> assumption that justify_hours must not depend on.

> Ahhh.  So the fact that justify_days already makes the 1 month == 30 days 
> assumption is ok in that function but can't be propagated to justify_hours.

Right.  I don't want us to define things so that none of this
functionality is available in situations where the 30-day assumption is
untenable.  justify_hours can still do something useful (ie, trim
oversize hours fields) without that.

justify_interval will probably be the new "normal" way to do things when
you are prepared to make both assumptions.  I'm not entirely sure about
the use-case for justify_days, but seems we ought to keep it for reasons
of backwards compatibility.
        regards, tom lane

On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote:
> Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:
>
> > > 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 do say both "quarter past three" (3 hours 15 min) and "quarter to
> four" (4 hours -15 min) when talking about time.

But the military says 1515 or 1545 or 0315 or 0345, because if they get
the time wrong they shell the wrong place and kill their own soldiers.
I.e. getting it right is important to them.  So they use exact
language.  I prefer the more exact way.

Ühel kenal päeval, N, 2006-03-02 kell 10:13, kirjutas Scott Marlowe:
> On Thu, 2006-03-02 at 00:45, Hannu Krosing wrote:
> > Ühel kenal päeval, K, 2006-03-01 kell 14:36, kirjutas Scott Marlowe:
..
> > > 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 do say both "quarter past three" (3 hours 15 min) and "quarter to
> > four" (4 hours -15 min) when talking about time.
> 
> But the military says 1515 or 1545 or 0315 or 0345, because if they get
> the time wrong they shell the wrong place and kill their own soldiers.

do they also speak so of intervals ? 

hannu=# select now(), now() - '-1 hour ago'::interval;             now              |           ?column?
-------------------------------+-------------------------------2006-03-02 19:47:38.042408+02 | 2006-03-02
18:47:38.042408+02
(1 row)

You see what I mean - perfectly precise and unconfusable :)

> I.e. getting it right is important to them.  So they use exact
> language.  I prefer the more exact way.

And in the current global era they must also speak in GMT all the time,
to avoid any confusion :P

--------------
Hannu

I've written the interval_justify() function but the parser does not know about 
it yet.  I changed these files:
  backend/utils/adt/timestamp.c  include/catalog/pg_proc.h  include/utils/timestamp.h

I used grep -R to find all locations where interval_justify_time is mentioned, 
and for each one added an analogous entry for my new function interval_justify.   But I get lost where OID=1175 is
associatedwith interval_justify_time.  I 
 
can't really just grep for 1175 and add analogous entries of my new OID, because 
I don't know what all the tables mean.  (BTW, I grabbed the next slot in the 
table and used OID=2711, but I don't know if that is the proper strategy for 
adding new OIDs.)

Is there an automated way of handling this task, such as a developer tool that I 
didn't find?

mark



Index: include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.399
diff -r1.399 pg_proc.h
1464a1465,1466> DATA(insert OID = 2711 (  justify                PGNSP PGUID 12 f f t f i 1 
1186 "1186" _null_ _null_ _null_  interval_justify - _null_ ));> DESCR("promote groups of 24 hours to numbers of days
andpromote groups of 30 
 
days to numbers of months");

On Thu, Mar 02, 2006 at 10:06:26AM -0800, Mark Dilger wrote:
> I used grep -R to find all locations where interval_justify_time is
> mentioned, and for each one added an analogous entry for my new function
> interval_justify. But I get lost where OID=1175 is associated with
>   interval_justify_time.  I can't really just grep for 1175 and add analogous
> entries of my new OID, because I don't know what all the tables mean.
> (BTW, I grabbed the next slot in the table and used OID=2711, but I don't
> know if that is the proper strategy for adding new OIDs.)
>
> Is there an automated way of handling this task, such as a developer tool
> that I didn't find?

In the include/catalog directory there are two script, unused_oids and
duplicate_oids. It tells you whats available.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Mark Dilger <pgsql@markdilger.com> writes:
> I've written the interval_justify() function but the parser does not know about 
> it yet.

The pg_proc change is the only source change you need for that, but
afterwards you need to update the postgres.bki file (handled by make and
make install in src/backend) and then initdb.
        regards, tom lane

Attached is the new patch.  To summarize:

   - new function justify_interval(interval)
   - modified function justify_hours(interval)
   - modified function justify_days(interval)

These functions are defined to meet the requirements as discussed in this
thread.  Specifically:

   - justify_hours makes certain the sign bit on the hours
     matches the sign bit on the days.  It only checks the
     sign bit on the days, and not the months, when
     determining if the hours should be positive or negative.
     After the call, -24 < hours < 24.

   - justify_days makes certain the sign bit on the days
     matches the sign bit on the months.  It's behavior does
     not depend on the hours, nor does it modify the hours.
     After the call, -30 < days < 30.

   - justify_interval makes sure the sign bits on all three
     fields months, days, and hours are all the same.  After
     the call, -24 < hours < 24 AND -30 < days < 30.

'make check' passes all tests.  There are no tests for justify_interval, as it
is new.  But the existing tests for justify_hours and justify_days appear to
still work, even though the behavior has changed.  Apparently, their test cases
are not sensitive to the particular changes that have occurred.

I would include new tests in the patch but do not know on which reference
machine/platform the patches are supposed to be generated.

mark
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.160
diff --context -r1.160 timestamp.c
*** src/backend/utils/adt/timestamp.c    22 Nov 2005 22:30:33 -0000    1.160
--- src/backend/utils/adt/timestamp.c    3 Mar 2006 20:23:26 -0000
***************
*** 1975,1980 ****
--- 1975,2054 ----
  }

  /*
+  *  interval_justify_interval()
+  *
+  *  Adjust interval so 'month', 'day', and 'time' portions are within
+  *  customary bounds.  Specifically:
+  *
+  *      0 <= abs(time) < 24 hours
+  *      0 <= abs(day)  < 30 days
+  *
+  *  Also, the sign bit on all three fields is made equal, so either
+  *  all three fields are negative or all are positive.
+  */
+ Datum
+ interval_justify_interval(PG_FUNCTION_ARGS)
+ {
+     Interval   *span = PG_GETARG_INTERVAL_P(0);
+     Interval   *result;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+     int64        wholeday;
+ #else
+     double        wholeday;
+ #endif
+     int32        wholemonth;
+
+     result = (Interval *) palloc(sizeof(Interval));
+     result->month = span->month;
+     result->day = span->day;
+     result->time = span->time;
+
+ #ifdef HAVE_INT64_TIMESTAMP
+     TMODULO(result->time, wholeday, USECS_PER_DAY);
+ #else
+     TMODULO(result->time, wholeday, (double) SECS_PER_DAY);
+ #endif
+     result->day += wholeday;    /* could overflow... */
+
+     wholemonth = result->day / DAYS_PER_MONTH;
+     result->day -= wholemonth * DAYS_PER_MONTH;
+     result->month += wholemonth;
+
+     if (result->month < 0 && result->day > 0)
+     {
+         result->day -= DAYS_PER_MONTH;
+         result->month++;
+     }
+     else if (result->month > 0 && result->day < 0)
+     {
+         result->day += DAYS_PER_MONTH;
+         result->month--;
+     }
+
+     if (result->time < 0 && result->day > 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time += USECS_PER_DAY;
+ #else
+         result->time += (double) SECS_PER_DAY;
+ #endif
+         result->day--;
+     }
+     else if (result->time > 0 && result->day < 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time -= USECS_PER_DAY;
+ #else
+         result->time -= (double) SECS_PER_DAY;
+ #endif
+         result->day++;
+     }
+
+     PG_RETURN_INTERVAL_P(result);
+ }
+
+ /*
   *    interval_justify_hours()
   *
   *    Adjust interval so 'time' contains less than a whole day, adding
***************
*** 2006,2011 ****
--- 2080,2104 ----
  #endif
      result->day += wholeday;    /* could overflow... */

+     if (result->time < 0 && result->day > 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time += USECS_PER_DAY;
+ #else
+         result->time += (double) SECS_PER_DAY;
+ #endif
+         result->day--;
+     }
+     else if (result->time > 0 && result->day < 0)
+     {
+ #ifdef HAVE_INT64_TIMESTAMP
+         result->time -= USECS_PER_DAY;
+ #else
+         result->time -= (double) SECS_PER_DAY;
+ #endif
+         result->day++;
+     }
+
      PG_RETURN_INTERVAL_P(result);
  }

***************
*** 2031,2036 ****
--- 2124,2140 ----
      result->day -= wholemonth * DAYS_PER_MONTH;
      result->month += wholemonth;

+     if (result->month < 0 && result->day > 0)
+     {
+         result->day -= DAYS_PER_MONTH;
+         result->month++;
+     }
+     else if (result->month > 0 && result->day < 0)
+     {
+         result->day += DAYS_PER_MONTH;
+         result->month--;
+     }
+
      PG_RETURN_INTERVAL_P(result);
  }

Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.399
diff --context -r1.399 pg_proc.h
*** src/include/catalog/pg_proc.h    28 Feb 2006 22:37:26 -0000    1.399
--- src/include/catalog/pg_proc.h    3 Mar 2006 20:23:27 -0000
***************
*** 1462,1467 ****
--- 1462,1469 ----
  DESCR("convert abstime to timestamp with time zone");
  DATA(insert OID = 1174 (  timestamptz       PGNSP PGUID 12 f f t f s 1 1184 "1082" _null_ _null_ _null_
date_timestamptz- _null_ )); 
  DESCR("convert date to timestamp with time zone");
+ DATA(insert OID = 2711 (  justify_interval PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_interval- _null_ )); 
+ DESCR("promote groups of 24 hours to numbers of days and promote groups of 30 days to numbers of months");
  DATA(insert OID = 1175 (  justify_hours    PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_hours- _null_ )); 
  DESCR("promote groups of 24 hours to numbers of days");
  DATA(insert OID = 1295 (  justify_days       PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_
interval_justify_days- _null_ )); 
Index: src/include/utils/timestamp.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/utils/timestamp.h,v
retrieving revision 1.57
diff --context -r1.57 timestamp.h
*** src/include/utils/timestamp.h    15 Oct 2005 02:49:46 -0000    1.57
--- src/include/utils/timestamp.h    3 Mar 2006 20:23:27 -0000
***************
*** 234,239 ****
--- 234,240 ----
  extern Datum interval_hash(PG_FUNCTION_ARGS);
  extern Datum interval_smaller(PG_FUNCTION_ARGS);
  extern Datum interval_larger(PG_FUNCTION_ARGS);
+ extern Datum interval_justify_interval(PG_FUNCTION_ARGS);
  extern Datum interval_justify_hours(PG_FUNCTION_ARGS);
  extern Datum interval_justify_days(PG_FUNCTION_ARGS);

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.

Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

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.  :)

Scott Marlowe <smarlowe@g2switchworks.com> writes:
> 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 semantics are perfectly well defined, so I don't buy this.
        regards, tom lane