Re: Understanding years part of Interval - Mailing list pgsql-general

> On 06/02/2023 18:33 CET Marcos Pegoraro <marcos@f10.com.br> wrote:
>
> Em seg., 6 de fev. de 2023 às 10:59, Erik Wienhold <ewie@ewie.name> escreveu:
> > > On 06/02/2023 12:20 CET Marcos Pegoraro <marcos@f10.com.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