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

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

From
PG Bug reporting form
Date:
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".


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

From
Alvaro Herrera
Date:
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



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

From
Tom Lane
Date:
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



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

From
Tomas Vondra
Date:
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



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

From
Francisco Olarte
Date:
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.



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

From
Alvaro Herrera
Date:
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



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

From
Tom Lane
Date:
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



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

From
Francisco Olarte
Date:
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.



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

From
Tom Lane
Date:
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