Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

Francisco Olarte <folarte@peoplecall.com> writes:
> On Sat, 17 Feb 2024 at 09:01, Michael Bondarenko
> <work.michael.2956@gmail.com> wrote:
>> When testing I stumbled upon that too, but I thought no calculation was happening in the interval field. However,
it'sdifferent with the days and 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=# 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

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 <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
     </para>

     <para>
-     Field values can have fractional parts:  for example, <literal>'1.5
+     Internally <type>interval</type> 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 standardized
+     way for output, for example:
+
+<programlisting>
+SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval;
+               interval
+---------------------------------------
+ 3 years 3 mons 700 days 133:17:36.789
+</programlisting>
+
+     Here weeks, which are understood as <quote>7 days</quote>, have been
+     kept separate, while the smaller and larger time units were
+     combined and normalized.  It is possible to use the
+     functions <function>justify_days</function>
+     and <function>justify_hours</function> to convert large days or
+     hours values into the next higher field:
+
+<programlisting>
+SELECT justify_days('2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::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 milliseconds'::interval);
+            justify_hours
+--------------------------------------
+ 3 years 3 mons 705 days 13:17:36.789
+</programlisting>
+    </para>
+
+    <para>
+     Input field values can have fractional parts, for example <literal>'1.5
      weeks'</literal> or <literal>'01:02:03.45'</literal>.  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. <literal>'1.5 years'</literal>
      becomes <literal>'1 year 6 mons'</literal>.  Fractional parts of
@@ -2922,33 +2958,6 @@ P <optional> <replaceable>years</replaceable>-<replaceable>months</replaceable>-
       </tgroup>
      </table>

-    <para>
-     Internally <type>interval</type> 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 integers
-     while the microseconds field can store fractional seconds.  Because intervals are
-     usually created from constant strings or <type>timestamp</type> subtraction,
-     this storage method works well in most cases, but can cause unexpected
-     results:
-
-<programlisting>
-SELECT EXTRACT(hours from '80 minutes'::interval);
- date_part
------------
-         1
-
-SELECT EXTRACT(days from '80 hours'::interval);
- date_part
------------
-         0
-</programlisting>
-
-     Functions <function>justify_days</function> and
-     <function>justify_hours</function> are available for adjusting days
-     and hours that overflow their normal ranges.
-    </para>
-
    </sect2>

    <sect2 id="datatype-interval-output">
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');
     </variablelist>
    </para>

+   <para>
+    When processing <type>interval</type> input,
+    the <function>extract</function> 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:
+<screen>
+SELECT INTERVAL '80 minutes';
+<lineannotation>Result: </lineannotation><computeroutput>01:20:00</computeroutput>
+SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
+<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
+</screen>
+   </para>
+
    <note>
     <para>
      When the input value is +/-Infinity, <function>extract</function> returns