Re: [SQL] Interval subtracting

Hi all,

Is there something incorrect in the above query ?
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' ?

/version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /

Thanks a lot,
Milorad Poluga
-------------------------------
milorad(dot)poluga(at)cores(dot)co(dot)yu

Milorad Poluga написа:
> Hi all,
>
> Is there something incorrect in the above query ?
> 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' ?
>
> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /

How many days are there in a month?

--
Milen A. Radev

On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
> Milorad Poluga написа:
> > Hi all,
> >
> > Is there something incorrect in the above query ?
> > 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' ?
> >
> > /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>
>
> How many days are there in a month?
>

I beleive that a month is calculated on the 30-days base.

One way to solve this problem is to use a neutal date element and make timestamps :

SELECT age(('1990-01-01'::date + '10 years 1 mons 1 days'::interval)::timestamp ,
('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)

age
--------------
2 mons 16 days

Regards,
Milorad Poluga

---------------------------------------
milorad(dot)poluga(at)cores(dot)co(dot)yu
---------------------------------------

Milorad Poluga написа:
> On Saturday 18 February 2006 15:24, Milen A. Radev wrote:
>> Milorad Poluga написа:
>>> Hi all,
>>>
>>> Is there something incorrect in the above query ?
>>> 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' ?
>>>
>>> /version = PostgreSQL 8.0.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3) /
>>
>> How many days are there in a month?
>>
>
> I beleive that a month is calculated on the 30-days base.

Are you sure? Where?

>
> One way to solve this problem is to use a neutal date element and make timestamps :
>
> SELECT age(('1990-01-01'::date + '10 years 1 mons 1 days'::interval)::timestamp ,
> ('1990-01-01'::date + '9 years 10 mons 15 days'::interval)::timestamp)
>
> age
> --------------
> 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.

--
Milen A. Radev

"Milen A. Radev" <milen(at)radev(dot)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(at)radev(dot)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'?

Thank you all for suggestions and links.
Currently, I am working on  PostgreSQL 8.0.4., so I cannot use justify_*() functions.

Regards,
Milorad Poluga

milorad(dot)poluga(at)cores(dot)co(dot)yu

Tom Lane wrote:
> "Milen A. Radev" <milen(at)radev(dot)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(at)radev(dot)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(at)radev(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Mark Dilger wrote:
> Mark Dilger wrote:
>
>> Tom Lane wrote:
>>
>>> "Milen A. Radev" <milen(at)radev(dot)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

The patch is attached. Since the functionality is being intentionally changed,
not surprisingly the regression tests for timestamp, timestamptz and horology
failed. The regression.diffs are also attached.

I intended to update the docs for justify_days and justify_hours, but the docs
don't detail the behavior at a sufficient level for any change to be warranted.

mark

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)skype(dot)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

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.

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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

Mark Dilger <pgsql(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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 day 12: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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)markdilger(dot)com> writes:
>
>>Am I correct that the second case should still have negative hours?
>
>
> Yes...
>
>
>>If so, then justify_hours(...) needs to examine the sign of the days
>>and months portion of the interval while performing its work.
>
>
> No, it should ignore the months part completely, IMHO. You are just
> confusing matters 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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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(at)markdilger(dot)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 quartes
past 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(at)markdilger(dot)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 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?

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 and promote 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(at)svana(dot)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(at)markdilger(dot)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

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(at)g2switchworks(dot)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

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

The interval data type is really useful. I see no reason to restrict its
usefulness with an arbitrary constraint. Date arithmetic is treacherous
and INTERVAL is a lifesaver.

Forcing a global sign on the interval would break interval arithmetic.
How would you compute '1 month'::interval - '1 week'::interval ?