Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rc7Lk-00120y-QV for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Feb 2024 17:22:41 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rc7Li-00GHG6-V4 for pgsql-bugs@arkaria.postgresql.org; Mon, 19 Feb 2024 17:22:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rc7Li-00GHFy-Jy for pgsql-bugs@lists.postgresql.org; Mon, 19 Feb 2024 17:22:38 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rc7Lf-0081TM-Oc for pgsql-bugs@lists.postgresql.org; Mon, 19 Feb 2024 17:22:38 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 41JHMX904116087; Mon, 19 Feb 2024 12:22:33 -0500 From: Tom Lane To: jian he cc: Francisco Olarte , Michael Bondarenko , pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); In-reply-to: References: <18348-b097a3587dfde8a4@postgresql.org>

<3709307.1708201825@sss.pgh.pa.us> Comments: In-reply-to jian he message dated "Sun, 18 Feb 2024 09:48:27 +0800" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <4115937.1708363332.0@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Mon, 19 Feb 2024 12:22:33 -0500 Message-ID: <4116086.1708363353@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="UTF-8" Content-ID: <4115937.1708363332.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit jian he writes: > 9.9.1. EXTRACT, date_part > hour field description as > ` > The hour field (0–23) > ` > Do we need to update for the EXTRACT(INTERVAL) case? Yeah, probably. I did a bit more wordsmithing too. Here's a rolled-up patch. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v2-interval-extract-docs-fixes.patch"; charset="us-ascii" Content-ID: <4115937.1708363332.2@sss.pgh.pa.us> Content-Description: v2-interval-extract-docs-fixes.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 66510ee031..73e51b0b11 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2869,10 +2869,31 @@ P years-months- = - Field values can have fractional parts: for example, '1.5 + Internally, interval values are stored as three integra= l + fields: months, days, and microseconds. These fields are kept + separate because the number of days in a month varies, while a day + can have 23 or 25 hours if a daylight savings time transition is + involved. An interval input string that uses other units is + normalized into this format, and then reconstructed in a standardize= d + way for output, for example: + + +SELECT '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::int= erval; + interval +--------------------------------------- + 3 years 3 mons 700 days 133:17:36.789 + + + Here weeks, which are understood as 7 days, have been + kept separate, while the smaller and larger time units were + combined and normalized. + + + + Input field values can have fractional parts, for example '= 1.5 weeks' or '01:02:03.45'. However, - because interval internally stores only three integer units (months, - days, microseconds), fractional units must be spilled to smaller + because interval internally stores only integral fields= , + fractional values must be converted into smaller units. Fractional parts of units greater than months are rounded to be an integer number of months, e.g. '1.5 years' becomes '1 year 6 mons'. Fractional parts of @@ -2922,33 +2943,6 @@ P years-months- = - - Internally interval values are stored as months, days, - and microseconds. 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 integer= s - while the microseconds field can store fractional seconds. Because = intervals are - usually created from constant strings or timestamp subt= raction, - this storage method works well in most cases, but can cause unexpect= ed - results: - - -SELECT EXTRACT(hours from '80 minutes'::interval); - date_part ------------ - 1 - -SELECT EXTRACT(days from '80 hours'::interval); - date_part ------------ - 0 - - - Functions justify_days and - justify_hours are available for adjusting days - and hours that overflow their normal ranges. - - = @@ -2960,6 +2954,23 @@ SELECT EXTRACT(days from '80 hours'::interval); formatting = + + As previously explained, PostgreSQL + stores interval values as months, days, and + microseconds. For output, the months field is converted to years an= d + months by dividing by 12. The days field is shown as-is. The + microseconds field is converted to hours, minutes, seconds, and + fractional seconds. Thus months, minutes, and seconds will never be + shown as exceeding the ranges 0–11, 0–59, and 0–59 + respectively, while the displayed years, days, and hours fields can + be quite large. (The justify_days<= /link> + and justify_hours + functions can be used if it is desirable to transpose large days or + hours values into the next higher field.) + + The output format of the interval type can be set to one of the four styles sql_standard, postgres, diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cf3de80394..3de13dae44 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -9166,7 +9166,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*= )){1,1}'); Subtract timestamps (converting 24-hour intervals into days, - similarly to justify_hours()) + similarly to justify_hours()) timestamp '2001-09-29 03:00' - timestamp '2001-07-27 12= :00' @@ -9579,35 +9580,35 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(= .*)){1,1}'); = - + justify_days justify_days ( interval ) interval - Adjust interval so 30-day time periods are represented as months + Adjust interval, converting 30-day time periods to months - justify_days(interval '35 days') - 1 mon 5 days + justify_days(interval '1 year 65 days') + 1 year 2 mons 5 days = - + justify_hours justify_hours ( interval ) interval - Adjust interval so 24-hour time periods are represented as days + Adjust interval, converting 24-hour time periods to days - justify_hours(interval '27 hours') - 1 day 03:00:00 + justify_hours(interval '50 hours 10 minutes') + 2 days 02:10:00 = @@ -10040,13 +10041,19 @@ EXTRACT(field FROM source) The extract function retrieves subfields such as year or hour from date/time values. source must be a value expression of - type timestamp, time, or interval. - (Expressions of type date are - cast to timestamp and can therefore be used as - well.) field is an identifier or + type timestamp, date, time, + or interval. (Timestamps and times can be with or + without time zone.) + field is an identifier or string that selects what field to extract from the source value. + Not all fields are valid for every input data type; for example, fiel= ds + smaller than a day cannot be extracted from a date, whil= e + fields of a day or more cannot be extracted from a time. The extract function returns values of type numeric. + + + The following are valid field names: = @@ -10063,17 +10070,11 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-1= 6 12:21:13'); Result: 20 SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 21 +SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); +Result: 1 +SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); +Result: -1 - - - The first century starts at 0001-01-01 00:00:00 AD, although - they did not know it at the time. This definition applies to all - Gregorian calendar countries. There is no century number 0, - you go from -1 century to 1 century. - - If you disagree with this, please write your complaint to: - Pope, Cathedral Saint-Peter of Roma, Vatican. - = @@ -10088,7 +10089,6 @@ SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 = 20:38:40'); SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 16 - SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute'); Result: 40 @@ -10162,10 +10162,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:= 38:40'); SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.1= 2-08'); Result: 982384720.120000= - SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40.12'); Result: 982355920.120000= - SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Result: 442800.000000 @@ -10193,7 +10191,8 @@ SELECT to_timestamp(982384720.12); hour - The hour field (0–23) + The hour field (0–23 in timestamps, unrestricted for + intervals) = @@ -10228,7 +10227,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 2= 0:38:40'); The ISO 8601 week-numbering year that the date - falls in (not applicable to intervals) + falls in = @@ -10256,7 +10255,7 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02'); The Julian Date corresponding to the - date or timestamp (not applicable to intervals). Timestamps + date or timestamp. Timestamps that are not local midnight result in a fractional value. See for more information. @@ -10345,10 +10344,8 @@ SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 = 20:38:40'); SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 2 - SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months'); Result: 3 - SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months'); Result: 1 @@ -10379,7 +10376,6 @@ SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 = 20:38:40'); SELECT EXTRACT(SECOND FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 40.000000 - SELECT EXTRACT(SECOND FROM TIME '17:12:28.5'); Result: 28.500000 @@ -10461,6 +10457,20 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20= :38:40'); = + + When processing interval input, + the extract function produces field values that + match the interpretation used by the interval output function. This + can produce surprising results if one starts with a non-normalized + interval representation, for example: + +SELECT INTERVAL '80 minutes'; +Result: 01:20:00 +SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes'); +Result: 20 + + + When the input value is +/-Infinity, extract re= turns @@ -10502,7 +10512,6 @@ date_part('field', source) SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40'); Result: 16 - SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); Result: 4 @@ -10580,16 +10589,12 @@ date_trunc(field, source [ SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-02-16 20:00= :00 - SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); Result: 2001-01-01 00:00= :00 - SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00= '); Result: 2001-02-16 00:00= :00-05 - SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40+00= ', 'Australia/Sydney'); Result: 2001-02-16 08:00= :00-05 - SELECT date_trunc('hour', INTERVAL '3 days 02:47:33'); Result: 3 days 02:00:00<= /computeroutput> @@ -10628,7 +10633,6 @@ date_bin(stride, source, < SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP = '2001-01-01'); Result: 2020-02-11 15:30= :00 - SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP = '2001-01-01 00:02:30'); Result: 2020-02-11 15:32= :30 @@ -10814,16 +10818,12 @@ SELECT date_bin('15 minutes', TIMESTAMP '2020-02= -11 15:44:17', TIMESTAMP '2001-0 SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver'; Result: 2001-02-16 19:38= :40-08 - SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'Am= erica/Denver'; Result: 2001-02-16 18:38= :40 - SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME = ZONE 'America/Chicago'; Result: 2001-02-16 05:38= :40 - SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL; Result: 2001-02-16 17:38= :40 - SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL; Result: 17:38:40 @@ -10928,16 +10928,12 @@ LOCALTIMESTAMP(precision) SELECT CURRENT_TIME; Result: 14:39:53.662522-= 05 - SELECT CURRENT_DATE; Result: 2019-12-23 - SELECT CURRENT_TIMESTAMP; Result: 2019-12-23 14:39= :53.662522-05 - SELECT CURRENT_TIMESTAMP(2); Result: 2019-12-23 14:39= :53.66-05 - SELECT LOCALTIMESTAMP; Result: 2019-12-23 14:39= :53.662522 ------- =_aaaaaaaaaa0--