From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Marek_L=C3=A4ll?= <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>
Subject: Re: BUG #18445: date_part / extract range for hours do not match
 documentation
In-reply-to: 
 <CADDPzFRkqATofspRzEBgnMVRUL8-JDu=3OO-DndeQnmKDMAfXg@mail.gmail.com>
References: <18445-2bdf6f3c730abe6d@postgresql.org>
 <3332891.1713883193@sss.pgh.pa.us>
 <CADDPzFRpa4Xb9xeSfxB9994JwPX+-fxLW5PuHWzKwccEt1Q_nA@mail.gmail.com>
 <CAKFQuwZs3WPRuTSm8xkJn-V-gWY-kyJXmb7vcb7CwtuZn08c4w@mail.gmail.com>
 <CADDPzFRkqATofspRzEBgnMVRUL8-JDu=3OO-DndeQnmKDMAfXg@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Marek_L=C3=A4ll?= <lall.marek@gmail.com>
	message dated "Fri, 26 Apr 2024 22:27:49 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <24614.1714162776.1@sss.pgh.pa.us>
Date: Fri, 26 Apr 2024 16:19:36 -0400
Message-ID: <24615.1714162776@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/24615.1714162776%40sss.pgh.pa.us>
Precedence: bulk

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