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(at)mit(dot)edu>
> Komu: Vik Fearing <vik(at)2ndquadrant(dot)fr>
> Datum: 05.09.2016 20:29
> Předmět: Re: BUG #14313: justify interval bug
>
> CC: <pgsql-bugs(at)postgresql(dot)org>
On Mon, Sep 5, 2016 at 6:59 PM, Vik Fearing <vik(at)2ndquadrant(dot)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