Re: BUG #18445: date_part / extract range for hours do not match documentation

Поиск
Список
Период
Сортировка
От Marek Läll
Тема Re: BUG #18445: date_part / extract range for hours do not match documentation
Дата
Msg-id CADDPzFRpa4Xb9xeSfxB9994JwPX+-fxLW5PuHWzKwccEt1Q_nA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BUG #18445: date_part / extract range for hours do not match documentation  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: BUG #18445: date_part / extract range for hours do not match documentation  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Дерево обсуждения
Hi!

psql (16.2, server 15.5) gives the following responses.
Time handling looks quite inconsistent.
Please take a look at 4 examples below.

My opinion is that example 2 is the only one which gives the correct result. Examples 1 and 3 should report errors as well.

Result of example 3 is extra nasty. It is like you have data type "byte" (valid range of values: 0 to 255) but there is a value 256 allowed as extra.



1) Please note that "00:00:60" is converted to "00:01:00":

# select time '00:00:60';
   time
----------
 00:01:00


2) Please note that "00:60:00" returns an error:

# select time '00:60:00';
ERROR:  date/time field value out of range: "00:60:00"
LINE 1: select time '00:60:00';
                    ^


3) Please note that "24:00:00" remains as it is "24:00:00"

 select time '24:00:00';
   time
----------
 24:00:00


4) Please note that "24:00:00" is considered as "00:00:00 +1 day" (NB! data type of column "difference" is interval):

# select *, (c - b) as difference, pg_typeof(c - b) as difference_type, extract(hours from b), extract(hours from c) from (select time '00:00:00' b, time '24:00:00' c) a;
    b     |    c     | difference | difference_type | extract | extract
----------+----------+------------+-----------------+---------+---------
 00:00:00 | 24:00:00 | 24:00:00   | interval        |       0 |      24


Regards
Marek Läll

Kontakt Tom Lane (<tgl@sss.pgh.pa.us>) kirjutas kuupäeval T, 23. aprill 2024 kell 17:39:
PG Bug reporting form <noreply@postgresql.org> writes:
> In https://www.postgresql.org/docs/8.1/functions-datetime.html

It would be a good idea to look at versions of the documentation
that aren't so many years obsolete.  8.1 has been EOL since 2010.

> We say that EXTRACT() should return a number between 0-23 for hours

What it says now is

        The hour field (0-23 in timestamps, unrestricted in intervals)

(This wording is currently only visible at
https://www.postgresql.org/docs/devel/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
but it will propagate into the website's copies of still-maintained
branches at our next quarterly updates.)

> The function seems to also return 24 in some cases eg:
> SELECT date_part('hour', cast('24:00:00.00' as time))::int
> Could we update the documentation or modify the function so that it wraps 24
> back to 0 hours?

That edge case for type "time" is deliberate; see the definition
of that type in table 8.9 here:

https://www.postgresql.org/docs/devel/datatype-datetime.html

I don't really feel a need to clutter the documentation for EXTRACT()
still more by mentioning it there, especially since the current
wording is not wrong, just silent about that detail.

                        regards, tom lane


В списке pgsql-bugs по дате отправления:

Предыдущее
От: Jacob Baskin
Дата:
Сообщение: Re: BUG #17690: Nonresponsive client on replica can halt replication indefinitely
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #18445: date_part / extract range for hours do not match documentation