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 <br...@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 +
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">
