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

​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

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&gt;

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