BUG #14313: justify interval bug

On 09/05/2016 06:31 PM, coolman.peto@centrum.cz wrote:

The following bug has been logged on the website:

Bug reference: 14313
Logged by: Peter Peterkys
Email address: coolman.peto@centrum.cz
PostgreSQL version: 9.3.14
Operating system: Ubuntu 14.04 LTS
Description:

The function "justify_interval" does not work properly.
E.g. if I subtract two different dates with the same month and with the same
day, but with different year, then I got greater interval than I should
get.
Please see my example

select justify_interval('2016-09-01'::timestamp - '1996-09-01') as test;

I got result:
'20 years 3 mons 15 days'

How is it possible?

Your timestamp subtraction results in 7305 days. The justify_interval
function, not knowing anything about where those days came from, will
use 30 days per month, giving you the result you see.

I should got the result:
'20 years' right?

Perhaps you want the age() function?

=# select age('2016-09-01'::timestamp, '1996-09-01');
age
----------
20 years
(1 row)

--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Mon, Sep 5, 2016 at 6:59 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:

Your timestamp subtraction results in 7305 days. The justify_interval
function, not knowing anything about where those days came from, will
use 30 days per month, giving you the result you see.

Part of the story that's not apparent here is that intervals track
months and days
separately but don't track years separately. So the justified interval
internally is actually 243 months, 15 days, and 0 seconds. The "20
years" is just part of the output format.

The reason years aren't tracked separately is that they're always 12
months. So if you want an event in your calendar that occurs every
year on the same date you can add an interval like '12 months' to it
and it'll always add exactly a year and land on the same date
regardless of the number of days in the year.

--
greg

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi,
thanks for your response.
 
Does it mean that if I use some timestamp and I add interval '20 years', it will be fine, but only if I try to use "justify interval" function, the inconsistency occur?
Now I understand the limits for using the function justify_interval ...
 
Best regards
Peter
 
______________________________________________________________

Od: Vik Fearing <vik@2ndquadrant.fr>
Komu: coolman.peto@centrum.cz, pgsql-bugs@postgresql.org
Datum: 05.09.2016 19:59
Předmět: Re: [BUGS] BUG #14313: justify interval bug

On 09/05/2016 06:31 PM, coolman.peto@centrum.cz wrote:

The following bug has been logged on the website:

Bug reference:      14313
Logged by:          Peter Peterkys
Email address:      coolman.peto@centrum.cz
PostgreSQL version: 9.3.14
Operating system:   Ubuntu 14.04 LTS
Description:        

The function "justify_interval" does not work properly.
E.g. if I subtract two different dates with the same month and with the same
day, but with different year, then I got greater interval than I should
get.
Please see my example

select justify_interval('2016-09-01'::timestamp - '1996-09-01') as test;

I got result:
'20 years 3 mons 15 days'

How is it possible?

Your timestamp subtraction results in 7305 days.  The justify_interval
function, not knowing anything about where those days came from, will
use 30 days per month, giving you the result you see.

I should got the result:
'20 years' right?

Perhaps you want the age() function?

=# select age('2016-09-01'::timestamp, '1996-09-01');
  age
----------
20 years
(1 row)

--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr <http://2ndQuadrant.fr&gt;     PostgreSQL : Expertise, Formation et Support

Hi,
my use case is that I need track event every 3 years and 10 months after previous event and keep in touch with object in upper limit for period of 3 years 10 months and 2 weeks.I performed comparison localtimestamp with the previous event date and if the interval was higher than interval of 3 years and 10 months and lower than interval of 3 years 10 months and 2 weeks, the the result is marked as target for next touch.
 
My question is, whether is it safe to subtract two timestamps and then compare interval without justify function with target interval (target interval is 3 years and 10 months, or 5 years and 10 months, or 7 years and 10 months, etc...). Or do I need recalculate the years of target interval to months?
If I subtract two timestamps, is the internally stored correct number of days in interval value?
 
Best regards
 
Peter
______________________________________________________________

Od: Greg Stark <stark@mit.edu>
Komu: Vik Fearing <vik@2ndquadrant.fr>
Datum: 05.09.2016 20:29
Předmět: Re: BUG #14313: justify interval bug

CC: <pgsql-bugs@postgresql.org>

On Mon, Sep 5, 2016 at 6:59 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:

Your timestamp subtraction results in 7305 days.  The justify_interval
function, not knowing anything about where those days came from, will
use 30 days per month, giving you the result you see.

Part of the story that's not apparent here is that intervals track
months and days
separately but don't track years separately. So the justified interval
internally is actually 243 months, 15 days, and 0 seconds. The "20
years" is just part of the output format.

The reason years aren't tracked separately is that they're always 12
months. So if you want an event in your calendar that occurs every
year on the same date you can add an interval like '12 months' to it
and it'll always add exactly a year and land on the same date
regardless of the number of days in the year.

--
greg

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs