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

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(at)sss(dot)pgh(dot)pa(dot)us>) kirjutas kuupäeval T, 23. aprill
2024 kell 17:39:

> PG Bug reporting form <noreply(at)postgresql(dot)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
>
>
>