Re: BUG #16169: Default time output for 24:00 is 00:00

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: folarte(at)peoplecall(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16169: Default time output for 24:00 is 00:00
Date: 2019-12-17 17:49:35
Message-ID: 6897.1576604975@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Alvaro Herrera <alvherre(at)2ndquadrant(dot)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)) 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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tomas Vondra 2019-12-17 17:56:55 Re: BUG #16169: Default time output for 24:00 is 00:00
Previous Message Alvaro Herrera 2019-12-17 17:26:07 Re: BUG #16169: Default time output for 24:00 is 00:00