Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

Adding another inconsistency I found in the docs to this thread (
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
):

The docs say: "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.)"

Which implies that the following two results must be the same:

tpch=# select extract(microseconds from date '1924.01.01');
ERROR: date units "microseconds" not supported

tpch=# select extract(microseconds from (date '1924.01.01')::timestamp);
extract
---------
0
(1 row)

However, the behaviour is different, which suggests that the date is indeed
treated as its own type in EXTRACT, and not cast to timestamp.

On Fri, Feb 16, 2024 at 2:07 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18348
> Logged by: Michael Bondarenko
> Email address: work(dot)michael(dot)2956(at)gmail(dot)com
> PostgreSQL version: 14.10
> Operating system: macOS
> Description:
>
> Hello,
>
> I'm building a random semantically-correct SQL code generator for
> PostgreSQL
> and I stumbled upon an inconsistency:
>
> tpch=# select extract(year from interval '3 years');
> extract
> ---------
> 3
> (1 row)
>
> tpch=# select extract(week from interval '3 weeks');
> ERROR: interval units "week" not supported
>
> In the documentation it's mentioned that 'week' is an ISO 8601 week, so it
> makes sense why it's not applicable to INTERVAL, which is the same for
> isoyear. However, the field is named week and not isoweek, so I expect it
> to
> work like the `select extract(year from interval '3 years');` does.
> 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
> .
>
>