From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Francisco Olarte <folarte(at)peoplecall(dot)com>, Michael Bondarenko <work(dot)michael(dot)2956(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org, dgrowleyml(at)gmail(dot)com |
Subject: | Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); |
Date: | 2024-02-18 01:48:27 |
Message-ID: | CACJufxGB4MD6vRz3jVGK6kza-6fuDgfW2jC_1S2+MwcQYGkgSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On Sun, Feb 18, 2024 at 4:30 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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?
From | Date | Subject | |
---|---|---|---|
Next Message | adfile@aol.com | 2024-02-18 01:51:59 | Postgresql16 dnf install postgresql16-devel.x86_64 fails - Resolve |
Previous Message | Tom Lane | 2024-02-17 20:30:25 | Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); |
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2024-02-18 01:59:55 | Re: Why is pq_begintypsend so slow? |
Previous Message | zwj | 2024-02-18 01:37:39 | bug report: some issues about pg_15_stable(8fa4a1ac61189efffb8b851ee77e1bc87360c445) |