Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbRKS-00FQJb-BK for pgsql-bugs@arkaria.postgresql.org; Sat, 17 Feb 2024 20:30:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rbRKQ-00847d-JJ for pgsql-bugs@arkaria.postgresql.org; Sat, 17 Feb 2024 20:30:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbRKQ-00847V-6t for pgsql-bugs@lists.postgresql.org; Sat, 17 Feb 2024 20:30:30 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbRKN-007G8a-Gg for pgsql-bugs@lists.postgresql.org; Sat, 17 Feb 2024 20:30:28 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 41HKUPCf3709308; Sat, 17 Feb 2024 15:30:25 -0500 From: Tom Lane To: Francisco Olarte cc: Michael Bondarenko , jian he , pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); In-reply-to: References: <18348-b097a3587dfde8a4@postgresql.org> Comments: In-reply-to Francisco Olarte message dated "Sat, 17 Feb 2024 16:12:15 +0100" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3709209.1708201798.0@sss.pgh.pa.us> Date: Sat, 17 Feb 2024 15:30:25 -0500 Message-ID: <3709307.1708201825@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <3709209.1708201798.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Francisco Olarte writes: > On Sat, 17 Feb 2024 at 09:01, Michael Bondarenko > wrote: >> When testing I stumbled upon that too, but I thought no calculation was= happening in the interval field. However, it's different with the days an= d months etc. It seems no calculation for day and month and more: >> ... >> But calculation is present for hour, and minutes and seconds (90061 sec= is 1 day 1 hour 1 minute 1 second): > No, intervals have seconds, days and months. Yeah. I think much of the confusion here comes from starting with non-normalized interval input. Sure you can write "2011 year 12 month 48 hour 1005 min 2 sec 11 ms", but that's not how it's stored: regression=3D# select interval '2011 year 12 month 48 hour 1005 min 2 sec = 11 ms'; interval = ------------------------- 2012 years 64:45:02.011 (1 row) (Actually, what's stored is 2012*12 months, 0 days, and some number of microseconds that I don't feel like working out. Conversion of the microseconds to HH:MM:SS.SSS happens on output.) Once you look at the normalized value, the results of extract() are far less surprising. Probably the right place to enlarge on this point is not in the extract() section at all, but in 8.5.4. Interval Input. That does mention the months/days/microseconds representation, but it doesn't follow through by illustrating how other input is combined. Perhaps we'd want to adopt something like the attached (this is separate from the other patches I posted in the thread). regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0003-clarify-interval-behavior.patch"; charset="us-ascii" Content-ID: <3709209.1708201798.2@sss.pgh.pa.us> Content-Description: 0003-clarify-interval-behavior.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 66510ee031..1c568e5022 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2869,10 +2869,46 @@ P years-months- = - Field values can have fractional parts: for example, '1.5 + Internally interval values are stored as three integral + fields: months, days, and microseconds. These fields are kept + separate because the number of days in a month varies, while a day + can have 23 or 25 hours if a daylight savings time transition is + involved. An interval input string that uses other units is + normalized into this format, and then reconstructed in a standardize= d + way for output, for example: + + +SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::int= erval; + interval +--------------------------------------- + 3 years 3 mons 700 days 133:17:36.789 + + + Here weeks, which are understood as 7 days, have been + kept separate, while the smaller and larger time units were + combined and normalized. It is possible to use the + functions justify_days + and justify_hours to convert large days or + hours values into the next higher field: + + +SELECT justify_days('2 years 15 months 100 weeks 99 hours 123456789 milli= seconds'::interval); + justify_days +-------------------------------------- + 5 years 2 mons 10 days 133:17:36.789 + +SELECT justify_hours('2 years 15 months 100 weeks 99 hours 123456789 mill= iseconds'::interval); + justify_hours +-------------------------------------- + 3 years 3 mons 705 days 13:17:36.789 + + + + + 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 + because interval internally stores only integer fields, + 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 @@ -2922,33 +2958,6 @@ P years-months- = - - 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 months and days fields are integer= s - while the microseconds field can store fractional seconds. Because = intervals are - usually created from constant strings or timestamp subt= raction, - this storage method works well in most cases, but can cause unexpect= ed - results: - - -SELECT EXTRACT(hours from '80 minutes'::interval); - date_part ------------ - 1 - -SELECT EXTRACT(days from '80 hours'::interval); - date_part ------------ - 0 - - - Functions justify_days and - justify_hours are available for adjusting days - and hours that overflow their normal ranges. - - = diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cf3de80394..480a8dcb60 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10461,6 +10461,20 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20= :38:40'); = + + When processing interval input, + the extract function produces field values that + match the interpretation used by the interval output function. This + can produce surprising results if one starts with a non-normalized + interval representation, for example: + +SELECT INTERVAL '80 minutes'; +Result: 01:20:00 +SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes'); +Result: 20 + + + When the input value is +/-Infinity, extract re= turns ------- =_aaaaaaaaaa0--