Received: from malur.postgresql.org ([2a02:16a8:dc51::56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1fVbyM-0006bU-Bv
	for pgsql-docs@arkaria.postgresql.org; Wed, 20 Jun 2018 12:12:26 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-docs-owner+archive@lists.postgresql.org>)
	id 1fVbyI-0007Bu-V2
	for pgsql-docs@arkaria.postgresql.org; Wed, 20 Jun 2018 12:12:22 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <bruce@momjian.us>)
	id 1fVbyI-0007Bl-PM
	for pgsql-docs@lists.postgresql.org; Wed, 20 Jun 2018 12:12:22 +0000
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.89)
	(envelope-from <bruce@momjian.us>)
	id 1fVbyF-0000Fp-Cd
	for pgsql-docs@lists.postgresql.org; Wed, 20 Jun 2018 12:12:22 +0000
Received: from bruce by momjian.us with local (Exim 4.84_2)
	(envelope-from <bruce@momjian.us>)
	id 1fVbyD-0004Ti-NJ; Wed, 20 Jun 2018 08:12:17 -0400
Date: Wed, 20 Jun 2018 08:12:17 -0400
From: Bruce Momjian <bruce@momjian.us>
To: vodevsh@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: DATE_PART('field', INTERVAL) not intuitive literal reading of
 interval
Message-ID: <20180620121217.GA7038@momjian.us>
References: <152698651482.26744.15456677499485530703@wrigleys.postgresql.org>
 <20180620035839.GA32746@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="HlL+5n6rz5pIUxbD"
Content-Disposition: inline
In-Reply-To: <20180620035839.GA32746@momjian.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
List-Id: <pgsql-docs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-docs@lists.postgresql.org>
List-Owner: <mailto:pgsql-docs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-docs>
Precedence: bulk


--HlL+5n6rz5pIUxbD
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Tue, Jun 19, 2018 at 11:58:39PM -0400, Bruce Momjian wrote:
> On Tue, May 22, 2018 at 10:55:14AM +0000, PG Doc comments form wrote:
> > https://stackoverflow.com/questions/50465632/postgresql-10-4-date-difference/50465676#50465676
> 
> This is really a function of how interval computes months, days, and
> seconds from subtraction, which is outlined here:
> 
> 	https://www.postgresql.org/docs/10/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
> 
> I don't think adding something to the functions-datetime.html section
> makes sense.  For example, this returns 1:
> 
> 	SELECT extract(minutes from '1 hour 1 minute'::interval);
> 	 date_part
> 	-----------
>         	 1

Thinking some more, I wonder if this behavior should be more clearly
documented:

	SELECT EXTRACT(hours from '80 minutes'::interval);
	 date_part
	-----------
	         1
	
	SELECT EXTRACT(days from '80 hours'::interval);
	 date_part
	-----------
	         0

To me, this clearly shows the behavior of the months, days, and seconds
components.  I have developed the attached doc patch to mention this.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--HlL+5n6rz5pIUxbD
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="interval.diff"

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
new file mode 100644
index 67bae32..cc54cc8
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
*************** P <optional> <replaceable>years</replace
*** 2671,2689 ****
      </para>
  
      <para>
-      Internally <type>interval</type> values are stored as months, days,
-      and seconds. 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 seconds field can store fractions.  Because intervals are
-      usually created from constant strings or <type>timestamp</type> subtraction,
-      this storage method works well in most cases. Functions
-      <function>justify_days</function> and <function>justify_hours</function> are
-      available for adjusting days and hours that overflow their normal
-      ranges.
-     </para>
- 
-     <para>
       In the verbose input format, and in some fields of the more compact
       input formats, field values can have fractional parts; for example
       <literal>'1.5 week'</literal> or <literal>'01:02:03.45'</literal>.  Such input is
--- 2671,2676 ----
*************** P <optional> <replaceable>years</replace
*** 2734,2739 ****
--- 2721,2753 ----
        </tgroup>
       </table>
  
+     <para>
+      Internally <type>interval</type> values are stored as months, days,
+      and seconds. 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 seconds field can store fractions.  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">

--HlL+5n6rz5pIUxbD--