Re: Understanding years part of Interval

> On 06/02/2023 18:33 CET Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
>
> Em seg., 6 de fev. de 2023 às 10:59, Erik Wienhold <ewie(at)ewie(dot)name> escreveu:
> > > On 06/02/2023 12:20 CET Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> wrote:
> > >
> > > I was just playing with some random timestamps for a week, for a month,
> > > for a year ...
> > >
> > > select distinct current_date+((random()::numeric)||'month')::interval from generate_series(1,100) order by 1;
> > > It´s with distinct clause because if you change that 'month' for a 'year'
> > > it´ll return only 12 rows, instead of 100. So, why years part of interval
> > > works differently than any other ?
> > >
> > > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 48.00 secs
> > > select '1.01 month'::interval; --> 0 years 1 mons 0 days 7 hours 12 mins 0.00 secs
> > > select '1.01 year'::interval; --> 1 years 0 mons 0 days 0 hours 0 mins 0.00 secs
> >
> > Explained in https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT:
> >
> > Field values can have fractional parts: for example, '1.5 weeks' or
> > '01:02:03.45'. However, because interval internally stores only
> > three integer units (months, days, microseconds), fractional units
> > must be spilled to smaller units. Fractional parts of units greater
> > than months are rounded to be an integer number of months, e.g.
> > '1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and
> > days are computed to be an integer number of days and microseconds,
> > assuming 30 days per month and 24 hours per day, e.g., '1.75 months'
> > becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as
> > fractional on output.
> >
> > Internally interval values are stored as months, days, and
> > microseconds. This is done because the number of days in a month
> > varies, and a day can have 23 or 25 hours if a daylight savings time
> > adjustment is involved.
> >
> I´ve sent this message initially to general and Erik told me it's documented,
> so it's better to hackers help me if this has an explaining why it's done that way.
>
> select '1 year'::interval = '1.05 year'::interval -->true ?
> I cannot agree that this select returns true.

The years are converted to months and the fractional month is rounded half up:

1.05 year = 12.6 month
=> 1 year 0.6 month
=> 1 year 1 month (after rounding)

Compare that to 12.5 months to see when the rounding occurs:

12.5 month / 12 month
=> 1.0416... years

Plug 1.0416 and 1.0417 into the interval to observe the rounding:

=# select '1.0416 year'::interval, '1.0417 year'::interval;
interval | interval
----------+--------------
1 year | 1 year 1 mon

--
Erik