Thread: BUG #18445: date_part / extract range for hours do not match documentation
BUG #18445: date_part / extract range for hours do not match documentation
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 18445 Logged by: Filipe Caldas Email address: fcaldasdesou@bloomberg.net PostgreSQL version: 14.11 Operating system: Linux (RHEL 8) Description: In https://www.postgresql.org/docs/8.1/functions-datetime.html We say that EXTRACT() should return a number between 0-23 for hours hour The hour field (0 - 23) SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 20 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? Thanks
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
Re: BUG #18445: date_part / extract range for hours do not match documentation
From
Marek Läll
Date:
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
Re: BUG #18445: date_part / extract range for hours do not match documentation
From
"David G. Johnston"
Date:
On Friday, April 26, 2024, Marek Läll <lall.marek@gmail.com> wrote:
Hi!psql (16.2, server 15.5) gives the following responses.Time handling looks quite inconsistent.
That’s probably a fair assessment.
My opinion is that example 2 is the only one which gives the correct result. Examples 1 and 3 should report errors as well.
We tend not to introduce breaking changes if the only motivation is to be consistent.
David J.
Re: BUG #18445: date_part / extract range for hours do not match documentation
From
Marek Läll
Date:
We tend not to introduce breaking changes if the only motivation is to be consistent.
Other mistakes are minor, but why is time '24:00:00' allowed, and it's actually 00:00:00 of the next day, that's something I'd like to read a well-argued design decision.
It's like months 1 through 12, and just in case, we also allow month 13, which represents January of the next year. But month 14 is not allowed, which could represent February of the next year.
Could you share the rationale behind this decision?
Marek Läll
Re: BUG #18445: date_part / extract range for hours do not match documentation
From
"David G. Johnston"
Date:
On Fri, Apr 26, 2024, 12:28 Marek Läll <lall.marek@gmail.com> wrote:
We tend not to introduce breaking changes if the only motivation is to be consistent.Other mistakes are minor, but why is time '24:00:00' allowed, and it's actually 00:00:00 of the next day, that's something I'd like to read a well-argued design decision.It's like months 1 through 12, and just in case, we also allow month 13, which represents January of the next year. But month 14 is not allowed, which could represent February of the next year.Could you share the rationale behind this decision?
It can be easier to construct "< date 24:00:00" to represent until the end of date rather than producing "< date+1 00:00:00"
David J.
=?UTF-8?Q?Marek_L=C3=A4ll?= <lall.marek@gmail.com> writes: >> We tend not to introduce breaking changes if the only motivation is to be >> consistent. > Other mistakes are minor, but why is time '24:00:00' allowed, and it's > actually 00:00:00 of the next day, that's something I'd like to read a > well-argued design decision. [ shrug... ] You're about twenty-five years too late to argue about this. The SQL spec does say that the HOUR field of a time value should be 0-23, so allowing '24:00:00' is an extension, most likely decided by Thomas Lockhart who wrote most of PG's datetime code to begin with. He's long gone from the project and probably doesn't remember his exact reasoning anyway. But we're not likely to remove that extension now, because there might be applications out there depending on it, and it's quite unclear what it's hurting. Arguing from principles of consistency when discussing common timekeeping rules is pointless anyway --- what in the world is consistent about any of it? It could be that Thomas deemed this a more sensible representation of '23:59:60', an input that's explicitly allowed by the SQL spec. But that's just guessing. regards, tom lane