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 1pP21j-0003ZX-3v for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 13:59: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 1pP21h-0007tw-RC for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 13:59:21 +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 1pP21h-0007tl-6P for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 13:59: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 1pP21e-0006L6-C1 for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 13:59:19 +0000 Received: from smtp102.mailbox.org (smtp102.mailbox.org [IPv6:2001:67c:2050:b231:465::102]) (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 4P9SYD04ZGz9sRG; Mon, 6 Feb 2023 14:59:12 +0100 (CET) Date: Mon, 6 Feb 2023 14:59:11 +0100 (CET) From: Erik Wienhold To: Marcos Pegoraro , "pgsql-generallists.postgresql.org" Message-ID: <1891019909.4271.1675691951281@office.mailbox.org> In-Reply-To: References: 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: 4P9SYD04ZGz9sRG List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 06/02/2023 12:20 CET Marcos Pegoraro 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 fro= m generate_series(1,100) order by 1; > It=C2=B4s with distinct clause because if you change that 'month' for a '= year' > it=C2=B4ll return only 12 rows, instead of 100. So, why years=C2=A0part o= f interval > works differently than any other ? > > select '1.01 week'::interval; --> 0 years 0 mons 7 days 1 hours 40 mins 4= 8.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#DATA= TYPE-INTERVAL-INPUT: =09Field values can have fractional parts: for example, '1.5 weeks' or =09'01:02:03.45'. However, because interval internally stores only =09three integer units (months, days, microseconds), fractional units =09must be spilled to smaller units. Fractional parts of units greater =09than months are rounded to be an integer number of months, e.g. =09'1.5 years' becomes '1 year 6 mons'. Fractional parts of weeks and =09days are computed to be an integer number of days and microseconds, =09assuming 30 days per month and 24 hours per day, e.g., '1.75 months' =09becomes 1 mon 22 days 12:00:00. Only seconds will ever be shown as =09fractional on output. =09Internally interval values are stored as months, days, and =09microseconds. This is done because the number of days in a month =09varies, and a day can have 23 or 25 hours if a daylight savings time =09adjustment is involved. -- Erik