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

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