Re: Understanding years part of Interval

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.