pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
4+ messages / 2 participants
[nested] [flat]
* Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
@ 2018-06-20 03:58 Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2018-06-20 03:58 UTC (permalink / raw)
To: vodevsh@gmail.com; pgsql-docs@lists.postgresql.org
On Tue, May 22, 2018 at 10:55:14AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/static/functions-datetime.html
> Description:
>
> the result of
> t=# SELECT DATE_PART('month', '2018-05-31'::timestamp -
> '2018-02-24'::timestamp);
> date_part
> -----------
> 0
> (1 row)
>
> looks correct, but not intuitive, maybe we need a NB for:
>
> t=# SELECT DATE_PART('month', justify_interval('2018-05-31'::timestamp -
> '2018-02-24'::timestamp));
> date_part
> -----------
> 3
> (1 row)
>
> 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
(Uh, it is kind of odd for EXTRACT to return a date_part column label.)
The point is that the subtraction doesn't justify the values.
--
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
@ 2018-06-20 12:12 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Bruce Momjian @ 2018-06-20 12:12 UTC (permalink / raw)
To: vodevsh@gmail.com; pgsql-docs@lists.postgresql.org
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) download
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">
* Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
@ 2018-06-20 12:42 Vladimir Svedov <vodevsh@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Vladimir Svedov @ 2018-06-20 12:42 UTC (permalink / raw)
To: bruce@momjian.us; +Cc: pgsql-docs@lists.postgresql.org
On Wed, 20 Jun 2018 at 15:12, Bruce Momjian <bruce@momjian.us> wrote:
> 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 +
>
Yes, it covers my question great. Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
@ 2018-06-24 03:33 Bruce Momjian <bruce@momjian.us>
parent: Vladimir Svedov <vodevsh@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2018-06-24 03:33 UTC (permalink / raw)
To: Vladimir Svedov <vodevsh@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Wed, Jun 20, 2018 at 03:42:54PM +0300, Vladimir Svedov wrote:
> Yes, it covers my question great. Thank youÂ
Patch applied back through 9.3. Thanks for the feedback.
--
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 +
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-06-24 03:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-20 03:58 Re: DATE_PART('field', INTERVAL) not intuitive literal reading of interval Bruce Momjian <bruce@momjian.us>
2018-06-20 12:12 ` Bruce Momjian <bruce@momjian.us>
2018-06-20 12:42 ` Vladimir Svedov <vodevsh@gmail.com>
2018-06-24 03:33 ` Bruce Momjian <bruce@momjian.us>
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