pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18445: date_part / extract range for hours do not match documentation
7+ messages / 4 participants
[nested] [flat]
* BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-23 10:10 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: PG Bug reporting form @ 2024-04-23 10:10 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: fcaldasdesou@bloomberg.net
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-23 14:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2024-04-23 14:39 UTC (permalink / raw)
To: fcaldasdesou@bloomberg.net; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-26 10:23 Marek Läll <lall.marek@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marek Läll @ 2024-04-26 10:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: fcaldasdesou@bloomberg.net; pgsql-bugs@lists.postgresql.org
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:
> 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
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-26 13:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Marek Läll <lall.marek@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2024-04-26 13:06 UTC (permalink / raw)
To: Marek Läll <lall.marek@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; fcaldasdesou@bloomberg.net <fcaldasdesou@bloomberg.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-26 19:27 Marek Läll <lall.marek@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Marek Läll @ 2024-04-26 19:27 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; fcaldasdesou@bloomberg.net <fcaldasdesou@bloomberg.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
>
> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-26 19:36 David G. Johnston <david.g.johnston@gmail.com>
parent: Marek Läll <lall.marek@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: David G. Johnston @ 2024-04-26 19:36 UTC (permalink / raw)
To: Marek Läll <lall.marek@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; fcaldasdesou@bloomberg.net, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
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.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #18445: date_part / extract range for hours do not match documentation
@ 2024-04-26 20:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marek Läll <lall.marek@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2024-04-26 20:19 UTC (permalink / raw)
To: Marek Läll <lall.marek@gmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; fcaldasdesou@bloomberg.net <fcaldasdesou@bloomberg.net>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
=?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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-04-26 20:19 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-23 10:10 BUG #18445: date_part / extract range for hours do not match documentation PG Bug reporting form <noreply@postgresql.org>
2024-04-23 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-04-26 10:23 ` Marek Läll <lall.marek@gmail.com>
2024-04-26 13:06 ` David G. Johnston <david.g.johnston@gmail.com>
2024-04-26 19:27 ` Marek Läll <lall.marek@gmail.com>
2024-04-26 19:36 ` David G. Johnston <david.g.johnston@gmail.com>
2024-04-26 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox