Thread: Re: Understanding years part of Interval

> 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.

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

I understood what you explained, but cannot agree that it's correct.

Run these and you'll see the first and second select are fine, the third ... why ?

select distinct current_date + ((random()::numeric) * '1 year'::interval) from generate_series(1,100) order by 1;

select distinct current_date + ((random()::numeric) * '12 month'::interval) from generate_series(1,100) order by 1;
select distinct current_date + ((random()::numeric) || 'year')::interval from generate_series(1,100) order by 1;

So, I have to think ... never use fractional parts on years, right ?