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