Peter Eisentraut <peter.eisentr...@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 <br...@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
