Re: BUG #14313: justify interval bug

On 09/05/2016 06:31 PM, coolman(dot)peto(at)centrum(dot)cz wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14313
> Logged by: Peter Peterkys
> Email address: coolman(dot)peto(at)centrum(dot)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