public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: David Rowley <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: Peter Eisentraut <[email protected]>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Date: Sat, 17 Feb 2024 13:14:19 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvr_-05ce6FaLM0P=hJBdV=Ht3=sdk_Eq__7YOf+fs5x9g@mail.gmail.com>
References: <[email protected]>
<CAApHDvr_-05ce6FaLM0P=hJBdV=Ht3=sdk_Eq__7YOf+fs5x9g@mail.gmail.com>
David Rowley <[email protected]> writes:
> On Sat, 17 Feb 2024 at 01:27, PG Bug reporting form
> <[email protected]> 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#FUNCTIONS-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
Attachments:
[text/x-diff] 0001-update-extract-description.patch (2.1K, ../[email protected]/2-0001-update-extract-description.patch)
download | inline diff:
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(<replaceable>field</replaceable> FROM <replaceable>source</replaceable>)
The <function>extract</function> function retrieves subfields
such as year or hour from date/time values.
<replaceable>source</replaceable> must be a value expression of
- type <type>timestamp</type>, <type>time</type>, or <type>interval</type>.
- (Expressions of type <type>date</type> are
- cast to <type>timestamp</type> and can therefore be used as
- well.) <replaceable>field</replaceable> is an identifier or
+ type <type>timestamp</type>, <type>date</type>, <type>time</type>,
+ or <type>interval</type>. (Timestamps and times can be with or
+ without time zone.)
+ <replaceable>field</replaceable> 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, fields
+ smaller than a day cannot be extracted from a <type>date</type>, while
+ fields of a day or more cannot be extracted from a <type>time</type>.
The <function>extract</function> function returns values of type
<type>numeric</type>.
+ </para>
+
+ <para>
The following are valid field names:
<!-- alphabetical -->
@@ -10228,7 +10228,7 @@ SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
<listitem>
<para>
The <acronym>ISO</acronym> 8601 week-numbering year that the date
- falls in (not applicable to intervals)
+ falls in
</para>
<screen>
@@ -10256,7 +10256,7 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
<listitem>
<para>
The <firstterm>Julian Date</firstterm> 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
<xref linkend="datetime-julian-dates"/> for more information.
</para>
[text/x-diff] 0002-condense-century-discussion.patch (1.2K, ../[email protected]/3-0002-condense-century-discussion.patch)
download | inline diff:
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-16 12:21:13');
<lineannotation>Result: </lineannotation><computeroutput>20</computeroutput>
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-16 20:38:40');
<lineannotation>Result: </lineannotation><computeroutput>21</computeroutput>
+SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD');
+<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>
+SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC');
+<lineannotation>Result: </lineannotation><computeroutput>-1</computeroutput>
</screen>
-
- <para>
- 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.
- </para>
</listitem>
</varlistentry>
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox