From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Ilya Ashchepkov <koctep(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: justify_interval: days in year |
Date: | 2014-11-10 13:14:15 |
Message-ID: | CA+bJJbyjXmG-XxuEDRzsMpEQ68JXN764ARdSMKKC1RH6X6GjPw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Nov 10, 2014 at 10:07 AM, Ilya Ashchepkov <koctep(at)gmail(dot)com> wrote:
> Is this correct?
> $ select
> justify_interval(t-f-i)
> ,t-f-i
> from
> (
> select
> timestamp '2014-1-1' as f,
> timestamp '2015-1-1' as t,
> interval '06:49:00' as i
> ) S;
>
> justify_interval | ?column?
> ------------------------+--------------------
> 1 year 4 days 17:11:00 | 365 days -06:49:00
>
> Why do we have 360 days in a year?
>
> Seems to be, the docs says:
justify_days(interval) interval Adjust interval so 30-day time periods are
represented as months justify_days(interval '35 days') 1 mon 5 days
justify_hours(interval) interval Adjust interval so 24-hour time periods
are represented as days justify_hours(interval '27 hours') 1 day 03:00:00
justify_interval(interval) interval Adjust interval using justify_days and
justify_hours, with additional sign adjustments justify_interval(interval
'1 mon -1 hour') 29 days 23:00:00
And, IIRC, an interval stores 3 parts, months, days and secods. The 365
days are justified to 12 months 5 days by justify days, then the 5 days -
6:49:0 are justified to 4 days 17:11:00 and then the 12 months are printed
as 1 year ( because months are printed as MM/12 years, MM%12 months ).
What I'm not sure is why the right column is stated as 365 days minus six
hours and change instead of 1 year minus six hours, maybe testing the parts
in sequence may shed light on it, but I wouldn't rely on justify_xxx for
any precise thing on big intervals given it's definition ( unless you are
working only with intervals and using 30 days months, 24 hour days
exclusively ).
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2014-11-10 13:29:14 | Re: Strange result using pg_dump gzip or split. |
Previous Message | John R Pierce | 2014-11-10 11:43:49 | Re: Strange result using pg_dump gzip or split. |