Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
| От | Michael Bondarenko |
|---|---|
| Тема | Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); |
| Дата | |
| Msg-id | CAF_O+z0EjWLt4vVgZeQ8vUC9ggT0hbXWPprv9-aCVhH=QTZ3MQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); (PG Bug reporting form <noreply@postgresql.org>) |
| Ответы |
Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
|
| Список | pgsql-bugs |
Adding another inconsistency I found in the docs to this thread (https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT):
tpch=# select extract(microseconds from (date '1924.01.01')::timestamp);
extract
---------
0
(1 row)
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
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@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 18348
Logged by: Michael Bondarenko
Email address: work.michael.2956@gmail.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
.
В списке pgsql-bugs по дате отправления: