Thread: justify_interval: days in year
Hello! 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?
On Mon, Nov 10, 2014 at 10:07 AM, Ilya Ashchepkov <koctep@gmail.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.
On Mon, 10 Nov 2014 14:14:15 +0100 Francisco Olarte <folarte@peoplecall.com> wrote: > On Mon, Nov 10, 2014 at 10:07 AM, Ilya Ashchepkov <koctep@gmail.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.