Interval subtracting

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.poluga@cores.co.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@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'?

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.poluga@cores.co.yu

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:

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

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