Thread: BUG #16169: Default time output for 24:00 is 00:00
The following bug has been logged on the website: Bug reference: 16169 Logged by: Francisco Olarte Email address: folarte@peoplecall.com PostgreSQL version: 12.0 Operating system: Documentation may need clarification. Description: Following a thread in the spanish mailing list I noticed the default time output for 24:00 is 00:00. ( checked using http://sqlfiddle.com/#!17/9eecb/43334 ). The output correctly follows what the manual says ( https://www.postgresql.org/docs/12/datatype-datetime.html ). Although the doc is correct, I have not been able to find any explicit mention of this behaviour. An explicit mention may be useful to avoid confusion, as one normally expects to be able to reconstruct inserted data from select results ( changing behaviour may hurt existing dependencies on current one ), and 2400 may be a common value when using time fields for things like "opening hours 16:00 - 24:00".
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 the > doc is correct, I have not been able to find any explicit mention of this > behaviour. An explicit mention may be useful to avoid confusion, as one > normally expects to be able to reconstruct inserted data from select results > ( changing behaviour may hurt existing dependencies on current one ), and > 2400 may be a common value when using time fields for things like "opening > 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. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> 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 the >> doc is correct, I have not been able to find any explicit mention of this >> behaviour. An explicit mention may be useful to avoid confusion, as one >> normally expects to be able to reconstruct inserted data from select results >> ( changing behaviour may hurt existing dependencies on current one ), and >> 2400 may be a common value when using time fields for things like "opening >> 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=# 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 from (values('00:00'::time), ('24:00'::time)) asv(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
On Tue, Dec 17, 2019 at 02:26:07PM -0300, Alvaro Herrera wrote: >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 the >> doc is correct, I have not been able to find any explicit mention of this >> behaviour. An explicit mention may be useful to avoid confusion, as one >> normally expects to be able to reconstruct inserted data from select results >> ( changing behaviour may hurt existing dependencies on current one ), and >> 2400 may be a common value when using time fields for things like "opening >> 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. > Yeah, I was wondering about the same thing. There's nothing like '24:00' for datetime (except when there's an extra second for a given day, perhaps). If someone needs to show opening hours or something, I don't see why to use datetime, we have 'time' data type for that, and that seems to be working fine: test=# select to_char('24:00:00'::time, 'HH24:MI:SS'); to_char ---------- 24:00:00 (1 row) Maybe I'm missing something, of course. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Tom: On Tue, Dec 17, 2019 at 6:49 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: ... > 8.5.1.2 should say no such thing, because the complaint as stated is > wrong. You are totally right, my fault. I apologize. Will try to remember to use a proper installation and select version() in the future. Francisco Olarte.
On 2019-Dec-17, Tom Lane wrote: > 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. Hm, interesting. SELECT version() in sqlfiddle's postgres 9.6 says: PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit As I remember, EDB would be visible. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Dec-17, Tom Lane wrote:
>> 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.
> Hm, interesting. SELECT version() in sqlfiddle's postgres 9.6 says:
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
> As I remember, EDB would be visible.
Another theory is that the webpage is passing the output through some
type-aware reformatting ... that seems pretty weird though.
regards, tom lane
On Tue, Dec 17, 2019 at 7:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Alvaro Herrera <alvherre@2ndquadrant.com> writes: > > On 2019-Dec-17, Tom Lane wrote: > >> 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. > > > Hm, interesting. SELECT version() in sqlfiddle's postgres 9.6 says: > > PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit > > As I remember, EDB would be visible. > > Another theory is that the webpage is passing the output through some > type-aware reformatting ... that seems pretty weird though. I think it is that. http://sqlfiddle.com/#!17/9eecb/43349 == select '24:00'::time::text; returns "24:00:00". Francisco Olarte.
Francisco Olarte <folarte@peoplecall.com> writes: > On Tue, Dec 17, 2019 at 7:18 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Another theory is that the webpage is passing the output through some >> type-aware reformatting ... that seems pretty weird though. > I think it is that. http://sqlfiddle.com/#!17/9eecb/43349 == select > '24:00'::time::text; returns "24:00:00". Hmm ... yeah. If you try similar experiments with interval or timestamp values, or even just booleans, you get completely different results depending on whether the exposed query result type is that or just text. For instance select now(); 2019-12-17T20:59:28.701655Z select now()::text; 2019-12-17 20:59:50.113717+00 There are a few cases where a cast-to-text in PG produces a different string than the type's output function would, but that doesn't apply here. sqlfiddle's web page must be coercing the value into some format they like better. So that's something sqlfiddle.com really ought to document for themselves. (Maybe they do --- I've never poked around their website in any detail.) regards, tom lane