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
|
| Список | 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
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';
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
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
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 по дате отправления: