Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihGyi-0003tn-De for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 17:49:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ihGyf-0006P0-I7 for pgsql-bugs@arkaria.postgresql.org; Tue, 17 Dec 2019 17:49:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1ihGyf-0006Ll-6L for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 17:49:45 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ihGyY-0007YJ-Do for pgsql-bugs@lists.postgresql.org; Tue, 17 Dec 2019 17:49:44 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id xBHHnZh9006898; Tue, 17 Dec 2019 12:49:35 -0500 From: Tom Lane To: Alvaro Herrera cc: folarte@peoplecall.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16169: Default time output for 24:00 is 00:00 In-reply-to: <20191217172607.GA10657@alvherre.pgsql> References: <20191217172607.GA10657@alvherre.pgsql> Comments: In-reply-to Alvaro Herrera message dated "Tue, 17 Dec 2019 14:26:07 -0300" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <6896.1576604975.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Tue, 17 Dec 2019 12:49:35 -0500 Message-ID: <6897.1576604975@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Alvaro Herrera writes: > On 2019-Dec-17, PG Bug reporting form wrote: >> The output correctly follows what the manual says ( >> https://www.postgresql.org/docs/12/datatype-datetime.html ). Although t= he >> doc is correct, I have not been able to find any explicit mention of th= is >> behaviour. An explicit mention may be useful to avoid confusion, as one >> normally expects to be able to reconstruct inserted data from select re= sults >> ( changing behaviour may hurt existing dependencies on current one ), a= nd >> 2400 may be a common value when using time fields for things like "open= ing >> hours 16:00 - 24:00". > Do you have a concrete proposal? I think 8.5.1.2 could have a new para > saying "note that 24:00 is displayed as 00:00 because it really is the > start of the next day, and there's no way to distinguish that from 00:00 > of the current day." Or maybe it should just state that using a time > column without an accompanying date column is a lost cause. 8.5.1.2 should say no such thing, because the complaint as stated is wrong. regression=3D# select '24:00'::time; = time = ---------- 24:00:00 (1 row) The referenced sqlfiddle example, which for our archives' sake is select t, extract(hours from t), extract(epoch from t), t-'00:00'::time fr= om (values('00:00'::time), ('24:00'::time)) as v(t); gives this for me: t | date_part | date_part | ?column? = ----------+-----------+-----------+---------- 00:00:00 | 0 | 0 | 00:00:00 24:00:00 | 24 | 86400 | 24:00:00 (2 rows) which doesn't seem particularly surprising either. However ... when you run it on sqlfiddle, that shows the 't' column as 00:00:00 in both cases. It also shows the fourth column in a format that doesn't match any of our intervalstyle formats. So there is something weird about sqlfiddle's "Postgres". I wonder whether it is actually EDB with the oracle compatibility hacks turned on. I tried "select version()" but that didn't show anything out of the ordinary. In any case, I don't think there is anything that needs changing about our docs. Some questions could be raised with the sqlfiddle maintainers, perhaps. regards, tom lane