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