Re: 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