From: | Ilya Ashchepkov <koctep(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: justify_interval: days in year |
Date: | 2014-11-11 03:09:05 |
Message-ID: | 20141111090905.1d9c9b89@moni.nskavd.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 10 Nov 2014 14:14:15 +0100
Francisco Olarte <folarte(at)peoplecall(dot)com> wrote:
> 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.
>
Thanks. I thought about justify_interval as function that formats
interval to human readable string. Justify_hours result is more
obvious. I will use it.
From | Date | Subject | |
---|---|---|---|
Next Message | rahulk | 2014-11-11 06:05:27 | Integrating PostgreSQL with Elasticsearch and Kibana |
Previous Message | Vinay Gupta | 2014-11-11 01:39:05 | Re: Concat bigint Array Values in a Function |