Обсуждение: DATE_PART('field', INTERVAL) not intuitive literal reading of interval
DATE_PART('field', INTERVAL) not intuitive literal reading of interval
От
PG Doc comments form
Дата:
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
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 +
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 +
Вложения
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
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 +