From: Bruce Momjian <bruce@momjian.us>
To: vodevsh@gmail.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
Date: Wed, 20 Jun 2018 08:12:17 -0400
Message-ID: <20180620121217.GA7038@momjian.us> (raw)
In-Reply-To: <20180620035839.GA32746@momjian.us>
References: <152698651482.26744.15456677499485530703@wrigleys.postgresql.org>
<20180620035839.GA32746@momjian.us>
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 +
Attachments:interval.diff (text/x-diff, 2.4K) downloaddiff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlnew file mode 100644index 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">
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, vodevsh@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
In-Reply-To: <20180620121217.GA7038@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox