pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: folarte@peoplecall.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16169: Default time output for 24:00 is 00:00
Date: Tue, 17 Dec 2019 12:49:35 -0500
Message-ID: <6897.1576604975@sss.pgh.pa.us> (raw)
In-Reply-To: <20191217172607.GA10657@alvherre.pgsql>
References: <20191217172607.GA10657@alvherre.pgsql>

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)) 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





view thread (9+ messages)  latest in thread

Message-ID: <6897.1576604975@sss.pgh.pa.us>
Permalink:  ../6897.1576604975@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/6897.1576604975@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, folarte@peoplecall.com
  Subject: Re: BUG #16169: Default time output for 24:00 is 00:00
  In-Reply-To: <6897.1576604975@sss.pgh.pa.us>
  References: <20191217172607.GA10657@alvherre.pgsql> <6897.1576604975@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox