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