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

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