Обсуждение: BUG #14313: justify interval bug
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMxMwpMb2dnZWQgYnk6ICAg ICAgICAgIFBldGVyIFBldGVya3lzCkVtYWlsIGFkZHJlc3M6ICAgICAgY29v bG1hbi5wZXRvQGNlbnRydW0uY3oKUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjMu MTQKT3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMTQuMDQgTFRTCkRlc2Ny aXB0aW9uOiAgICAgICAgCgpUaGUgZnVuY3Rpb24gImp1c3RpZnlfaW50ZXJ2 YWwiIGRvZXMgbm90IHdvcmsgcHJvcGVybHkuDQpFLmcuIGlmIEkgc3VidHJh Y3QgdHdvIGRpZmZlcmVudCBkYXRlcyB3aXRoIHRoZSBzYW1lIG1vbnRoIGFu ZCB3aXRoIHRoZSBzYW1lCmRheSwgYnV0IHdpdGggZGlmZmVyZW50IHllYXIs IHRoZW4gSSBnb3QgZ3JlYXRlciBpbnRlcnZhbCB0aGFuIEkgc2hvdWxkCmdl dC4NClBsZWFzZSBzZWUgbXkgZXhhbXBsZQ0KDQpzZWxlY3QganVzdGlmeV9p bnRlcnZhbCgnMjAxNi0wOS0wMSc6OnRpbWVzdGFtcCAtICcxOTk2LTA5LTAx JykgYXMgdGVzdDsNCg0KSSBnb3QgcmVzdWx0Og0KJzIwIHllYXJzIDMgbW9u cyAxNSBkYXlzJw0KDQpIb3cgaXMgaXQgcG9zc2libGU/IEkgc2hvdWxkIGdv dCB0aGUgcmVzdWx0Og0KJzIwIHllYXJzJyByaWdodD8KCg==
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
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
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 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 inupper limit for period of 3 years 10 months and 2 weeks.I performed comparison localtimestamp with the previous event dateand if the interval was higher than interval of 3 years and 10 months and lower than interval of 3 years 10 months and2 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 targetinterval (target interval is 3 years and 10 months, or 5 years and 10 months, or 7 years and 10 months, etc...). Ordo 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