On Sun, Feb 18, 2024 at 4:30 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>
> Once you look at the normalized value, the results of extract()
> are far less surprising.
>
> Probably the right place to enlarge on this point is not in the
> extract() section at all, but in 8.5.4. Interval Input. That does
> mention the months/days/microseconds representation, but it doesn't
> follow through by illustrating how other input is combined. Perhaps
> we'd want to adopt something like the attached (this is separate from
> the other patches I posted in the thread).
>
--- 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>
you already mentioned "Not all fields are valid for every input data type".
interval data type don't even have a unit "quarter",
so the following should generate an error?
select extract(quarter from interval '2011 year 12 month 48 hour
1005min 2 sec 11 ms');
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?