Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP7C3-0002pL-Bt for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 19:30:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pP7C2-0005Jx-03 for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 19:30:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP7C1-0005JM-LX for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 19:30:21 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pP7Bn-0000wW-OH for pgsql-general@postgresql.org; Mon, 06 Feb 2023 19:30:09 +0000 Received: from smtp202.mailbox.org (smtp202.mailbox.org [IPv6:2001:67c:2050:b231:465::202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4P9btt68NMz9sS8 for ; Mon, 6 Feb 2023 20:29:58 +0100 (CET) Date: Mon, 6 Feb 2023 20:29:58 +0100 (CET) From: Erik Wienhold To: "pgsql-general@postgresql.org" Message-ID: <371463617.216036.1675711798455@office.mailbox.org> In-Reply-To: <1803669038.215195.1675711296688@office.mailbox.org> References: <1891019909.4271.1675691951281@office.mailbox.org> <1803669038.215195.1675711296688@office.mailbox.org> Subject: Re: Understanding years part of Interval MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4P9btt68NMz9sS8 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 06/02/2023 18:33 CET Marcos Pegoraro wrote: > > Em seg., 6 de fev. de 2023 =C3=A0s 10:59, Erik Wienhold = escreveu: > > > On 06/02/2023 12:20 CET Marcos Pegoraro wrote: > > > > > > I was just playing with some random timestamps for a week, for a mon= th, > > > for a year ... > > > > > > select distinct current_date+((random()::numeric)||'month')::interva= l from generate_series(1,100) order by 1; > > > It=C2=B4s with distinct clause because if you change that 'month' fo= r a 'year' > > > it=C2=B4ll return only 12 rows, instead of 100. So, why years=C2=A0p= art of interval > > > works differently than any other ? > > > > > > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 m= ins 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 mi= ns 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=C2=B4ve sent this message initially to general and Erik told me it's do= cumented, > so it's better to hackers help me if this has an explaining why it's done= that way. > > select '1 year'::interval =3D '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: =091.05 year =3D 12.6 month =09=3D> 1 year 0.6 month =09=3D> 1 year 1 month (after rounding) Compare that to 12.5 months to see when the rounding occurs: =0912.5 month / 12 month =09=3D> 1.0416... years Plug 1.0416 and 1.0417 into the interval to observe the rounding: =09=3D# select '1.0416 year'::interval, '1.0417 year'::interval; =09 interval | interval =09----------+-------------- =09 1 year | 1 year 1 mon -- Erik