Обсуждение: BUG #18445: date_part / extract range for hours do not match documentation

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

Hi!

psql (16.2, server 15.5) gives the following responses.

Time handling looks quite inconsistent.

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

=?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