DATE_PART('field', INTERVAL) not intuitive literal reading of interval

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 +