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

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.
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:

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.

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

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