Обсуждение: What does Time.MAX_VALUE actually represent?

On 12/31/2017 03:07 AM, Dave Cramer wrote:
> We are having a discussion on the jdbc project about dealing with 
> 24:00:00.
>
> https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612
>
> Dave Cramer

In Dublin (I was there 2001 to 2004), Time tables show buses just after 
midnight, such as 1:20am as running at the time 2520 - so there are 
visible close to the end of the day.  If you are looking for buses 
around midnight this is very user friendly - better than looking at the 
other end of the time table for 0120.

I think logically that 24:00:00 is exactly one day later than 00:00:00 - 
but I see from following the URL, that there are other complications...


Cheers,
Gavin

On 12/30/17 09:07, Dave Cramer wrote:
> We are having a discussion on the jdbc project about dealing with 24:00:00.
> 
> https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612

select timestamp '2017-12-30 24:00:00';

returns

2017-12-31 00:00:00

which makes some sense.

I don't know why we accept that and not '24:00:01' and beyond, but it's
probably historical.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> select timestamp '2017-12-30 24:00:00';
> returns
> 2017-12-31 00:00:00
> which makes some sense.

> I don't know why we accept that and not '24:00:01' and beyond, but it's
> probably historical.

We also accept

regression=# select timestamp '2017-12-30 23:59:60';
      timestamp      
---------------------
 2017-12-31 00:00:00
(1 row)

which is maybe a little bit more defensible as a common behavior
to allow for leap seconds.  (It's far from a correct implementation of
leap seconds, of course, but I think most versions of mktime() and
friends do likewise.)

Digging into the git history, it looks like this choice dates to

commit a93bf4503ffc6d7cd6243a6324fb2ef206b10adf
Author: Bruce Momjian <bruce@momjian.us>
Date:   Fri Oct 14 11:47:57 2005 +0000

    Allow times of 24:00:00 to match rounding behavior:
    
        regression=# select '23:59:59.9'::time(0);
           time
        ----------
         24:00:00
        (1 row)
    
        This is bad because:
    
        regression=# select '24:00:00'::time(0);
        ERROR:  date/time field value out of range: "24:00:00"
    
    The last example now works.


There may at the time have been some argument about imprecision of
float timestamps involved, but it works the same way today once
you exceed the precision of our integer timestamps:

regression=# select time '23:59:59.999999';
      time       
-----------------
 23:59:59.999999
(1 row)

regression=# select time '23:59:59.9999999';
   time   
----------
 24:00:00
(1 row)

If we didn't allow '24:00:00' as a valid value then we'd need to
throw an error for '23:59:59.9999999', which doesn't seem nice.

            regards, tom lane

Hi Bear,

Please don't top post!

On 01/01/2018 06:17 AM, Bear Giles wrote:
> ​You don't need to store 25:20 in the database though - your app can 
> use a window that treats a day as "from 5 am today until 5 am 
> tomorrow" and adds 24:00 to the times for tomorrow.​
>
> Bear
>
> On Sat, Dec 30, 2017 at 2:25 PM, Gavin Flower 
> <GavinFlower@archidevsys.co.nz <mailto:GavinFlower@archidevsys.co.nz>> 
> wrote:
>
>     On 12/31/2017 03:07 AM, Dave Cramer wrote:
>
>         We are having a discussion on the jdbc project about dealing
>         with 24:00:00.
>
>         https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612
>         <https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612>
>
>         Dave Cramer
>
>
>     In Dublin (I was there 2001 to 2004), Time tables show buses just
>     after midnight, such as 1:20am as running at the time 2520 - so
>     there are visible close to the end of the day.  If you are looking
>     for buses around midnight this is very user friendly - better than
>     looking at the other end of the time table for 0120.
>
>     I think logically that 24:00:00 is exactly one day later than
>     00:00:00 - but I see from following the URL, that there are other
>     complications...
>
>
>     Cheers,
>     Gavin
>
>
>
Sorry, I did not mean to imply that the data base should store values 
24:00:00 and greater!

For something like a time table, values like "24:00" and "25:20" should 
be generated by software where (and if) appropriate.


Cheers,
Gavin

Moin,

On Sat, December 30, 2017 4:25 pm, Gavin Flower wrote:
> On 12/31/2017 03:07 AM, Dave Cramer wrote:
>> We are having a discussion on the jdbc project about dealing with
>> 24:00:00.
>>
>> https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612
>>
>> Dave Cramer
>
> In Dublin (I was there 2001 to 2004), Time tables show buses just after
> midnight, such as 1:20am as running at the time 2520 - so there are
> visible close to the end of the day.  If you are looking for buses
> around midnight this is very user friendly - better than looking at the
> other end of the time table for 0120.
>
> I think logically that 24:00:00 is exactly one day later than 00:00:00 -
> but I see from following the URL, that there are other complications...

Careful here, if "24:00:00" always means literally "00:00:00 one day
later", that could work, but you can't just have it meaning "add 24 hours
to the clock".

For instance, during daylight saving time changes, days can be 23 hours or
25 hours long...

Best wishes,

Tels

Moin,

On Sun, December 31, 2017 12:50 pm, Tom Lane wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> select timestamp '2017-12-30 24:00:00';
>> returns
>> 2017-12-31 00:00:00
>> which makes some sense.
>
>> I don't know why we accept that and not '24:00:01' and beyond, but it's
>> probably historical.
>
> We also accept
>
> regression=# select timestamp '2017-12-30 23:59:60';
>       timestamp
> ---------------------
>  2017-12-31 00:00:00
> (1 row)
>
> which is maybe a little bit more defensible as a common behavior
> to allow for leap seconds.  (It's far from a correct implementation of
> leap seconds, of course, but I think most versions of mktime() and
> friends do likewise.)
>
> Digging into the git history, it looks like this choice dates to
>
> commit a93bf4503ffc6d7cd6243a6324fb2ef206b10adf
> Author: Bruce Momjian <bruce@momjian.us>
> Date:   Fri Oct 14 11:47:57 2005 +0000
>
>     Allow times of 24:00:00 to match rounding behavior:
>
>         regression=# select '23:59:59.9'::time(0);
>            time
>         ----------
>          24:00:00
>         (1 row)
>
>         This is bad because:
>
>         regression=# select '24:00:00'::time(0);
>         ERROR:  date/time field value out of range: "24:00:00"
>
>     The last example now works.
>
>
> There may at the time have been some argument about imprecision of
> float timestamps involved, but it works the same way today once
> you exceed the precision of our integer timestamps:
>
> regression=# select time '23:59:59.999999';
>       time
> -----------------
>  23:59:59.999999
> (1 row)
>
> regression=# select time '23:59:59.9999999';
>    time
> ----------
>  24:00:00
> (1 row)
>
> If we didn't allow '24:00:00' as a valid value then we'd need to
> throw an error for '23:59:59.9999999', which doesn't seem nice.

Hm, but shouldn't the result then be "00:00:00" instead of "24:00:00"?

With addition it seems to work different:

postgres=# select time '23:59:59.999999' + interval '0.000001 seconds';
 ?column?
----------
 00:00:00
(1 row)

Best regards,

Tels

On 01/02/2018 01:26 AM, Tels wrote:
> Moin,
>
> On Sat, December 30, 2017 4:25 pm, Gavin Flower wrote:
>> On 12/31/2017 03:07 AM, Dave Cramer wrote:
>>> We are having a discussion on the jdbc project about dealing with
>>> 24:00:00.
>>>
>>> https://github.com/pgjdbc/pgjdbc/pull/992#issuecomment-354507612
>>>
>>> Dave Cramer
>> In Dublin (I was there 2001 to 2004), Time tables show buses just after
>> midnight, such as 1:20am as running at the time 2520 - so there are
>> visible close to the end of the day.  If you are looking for buses
>> around midnight this is very user friendly - better than looking at the
>> other end of the time table for 0120.
>>
>> I think logically that 24:00:00 is exactly one day later than 00:00:00 -
>> but I see from following the URL, that there are other complications...
> Careful here, if "24:00:00" always means literally "00:00:00 one day
> later", that could work, but you can't just have it meaning "add 24 hours
> to the clock".
>
> For instance, during daylight saving time changes, days can be 23 hours or
> 25 hours long...
>
> Best wishes,
>
> Tels

Agreed, I'm thinking purely of displayed time.  Where the utility of 
using times like 2400 and 2530 is purely the convenience of people 
looking to catching a bus after a late night out.

The 24:00 time should be referred to in a similar way to the notation of 
'0+' in limits (that is informally defined as the smallest positive real 
number -- formally that is nonsense, 'lim 0+' actually means approach 
the limit from the positive direction).


Cheers,
Gavin