Re: Conversion errors for datetime fields

* Tom Lane <tgl@sss.pgh.pa.us> [001228 11:33]:
> Larry Rosenman <ler@lerctr.org> writes:
> > Aren't we *REQUIRED* by SQL99 to accept up to :61 to account for
> > leap seconds?
>
> 60, maybe --- I have not looked at the SQL spec.  61 is a widely
> repeated mistake; there never have been and never will be two leap
> seconds in the same minute (cf. NTP spec, RFC1305, esp. appendix E).
> But in reality, since we are using Unix-based timekeeping which does not
> cope with leap seconds, it is pointless to consider :60 as meaning a
> leap second.  I think it's better to continue to regard it as an error.
> The only other thing we could do with it is treat 00:00:60 as meaning
> the same as 00:01:00, which is not really correct behavior.
Looking at Page 166 of "SQL-99 Complete, Really" by Peter Gulutzan &
Trudy Peltzer, R&D Books, ISBN 0-87930-568-1, 1st bullet:

   " First the Standard *REQUIRES* a DBMS to extend the range of
seconds-field values to ''less than 62'' (rather than ''less than
60'') and thus account for up to 2 positive leap seconds. (There is a
GOTCHA here: leap seconds should always be for the last minute of a
day as in TIME '23:59:60', but the Standard allows erroneous values
like TIME '12:34:60'.)" Emphasis on requires is mine.

So, here we have the SQL-99 standard requiring the behaviour.

So, what do the assembled coders/experts think?

Larry

>
>             regards, tom lane
--
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749