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 1rbPCm-00FGqy-HU for pgsql-bugs@arkaria.postgresql.org; Sat, 17 Feb 2024 18:14:28 +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 1rbPCl-007WnY-1M for pgsql-bugs@arkaria.postgresql.org; Sat, 17 Feb 2024 18:14:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbPCk-007WnQ-PE for pgsql-bugs@lists.postgresql.org; Sat, 17 Feb 2024 18:14:26 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rbPCh-007FKD-QT for pgsql-bugs@lists.postgresql.org; Sat, 17 Feb 2024 18:14:25 +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 41HIEJRe3693247; Sat, 17 Feb 2024 13:14:19 -0500 From: Tom Lane To: David Rowley cc: work.michael.2956@gmail.com, pgsql-bugs@lists.postgresql.org, Peter Eisentraut Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); In-reply-to: References: <18348-b097a3587dfde8a4@postgresql.org> Comments: In-reply-to David Rowley message dated "Sat, 17 Feb 2024 02:02:20 +1300" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3693194.1708193617.0@sss.pgh.pa.us> Date: Sat, 17 Feb 2024 13:14:19 -0500 Message-ID: <3693246.1708193659@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="us-ascii" Content-ID: <3693194.1708193617.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable David Rowley writes: > On Sat, 17 Feb 2024 at 01:27, PG Bug reporting form > wrote: >> Moreover, the documentation does not mention that the field cannot be >> extracted from INTERVAL, like it does for isoyear: >> https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIO= NS-DATETIME-EXTRACT > Maybe that table should specify which type(s) each of the items listed > is applicable to. Seems better than mentioning which types they're not > applicable to. The thing's not laid out as a table though, and converting it seems like more trouble than this is worth. The rejected cases hardly seem surprising. I propose just mentioning that not all fields apply for all data types, as in 0001 attached. (Parenthetically, one case that perhaps is surprising is ERROR: unit "week" not supported for type interval Why not just return the day field divided by 7?) Unrelated but adjacent, the discussion of the century field seems more than a bit flippant when I read it now. In other places we are typically content to use examples to make similar points. I propose doing so here too, as in 0002 attached. Lastly, the entire page is quite schizophrenic about whether to leave a blank line between adjacent examples. I could go either way on whether to have that whitespace or not, but I do think it would be better to make it uniform. Any votes on what to do there? regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0001-update-extract-description.patch"; charset="us-ascii" Content-ID: <3693194.1708193617.2@sss.pgh.pa.us> Content-Description: 0001-update-extract-description.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cf3de80394..fc8017f2f3 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10040,13 +10040,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: = @@ -10228,7 +10228,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 +10256,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. ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="0002-condense-century-discussion.patch"; charset="us-ascii" Content-ID: <3693194.1708193617.3@sss.pgh.pa.us> Content-Description: 0002-condense-century-discussion.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index cf3de80394..5d215d218c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10063,17 +10069,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. - = ------- =_aaaaaaaaaa0--