Re: Conversion errors for datetime fields

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> So, here we have the SQL-99 standard requiring the behaviour.

"Requiring"? The only SQL99 text I can find that mentions leap seconds is:

A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP
WITHOUT TIME ZONE, may represent a local time, whereas a datetime
value of data type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE
represents UTC. On occasion, UTC is adjusted by the omission of
a second or the insertion of a "leap second" in order to maintain
synchronization with sidereal time. This implies that sometimes,
but very rarely, a particular minute will contain exactly 59,
61, or 62 seconds. Whether an SQL-implementation supports leap
seconds, and the consequences of such support for date and interval
arithmetic, is implementation-defined.

So the SQL99 spec repeats the error that there could be two leap seconds
in the same minute :-(. I once read that that derives from one
particular erroneous document that a lot of people have slavishly
copied. I do not recall what it was, though. Anyway, RFC-1305 says:

The International Bureau of Weights and Measures (IBWM) uses
astronomical observations provided by the U.S. Naval Observatory and
other observatories to determine UTC. Starting from apparent mean solar
time as observed, the UT0 timescale is determined using corrections for
Earth orbit and inclination (the Equation of Time, as used by sundials),
the UT1 (navigator's) timescale by adding corrections for polar
migration and the UT2 timescale by adding corrections for known
periodicity variations. While standard frequencies are based on TAI,
conventional civil time is based on UT1, which is presently slowing
relative to TAI by a fraction of a second per year. When the magnitude
of correction approaches 0.7 second, a leap second is inserted or
deleted in the TAI timescale on the last day of June or December.

For the most precise coordination and timestamping of events since 1972,
it is necessary to know when leap seconds are implemented in UTC and how
the seconds are numbered. As specified in CCIR Report 517, which is
reproduced in [BLA74], a leap second is inserted following second
23:59:59 on the last day of June or December and becomes second 23:59:60
of that day. A leap second would be deleted by omitting second 23:59:59
on one of these days, although this has never happened. Leap seconds
were inserted prior to 1 January 1991 on the occasions listed in Table
8<$&tab8> (courtesy U.S. Naval Observatory). Published IBWM corrections
consist not only of leap seconds, which result in step discontinuities
relative to TAI, but 100-ms UT1 adjustments called DUT1, which provide
increased accuracy for navigation and space science.

But anyway, this is all academic. Since we are sitting atop Unix
timekeeping, which *does not* implement leap seconds, this
implementation is not going to support leap seconds. That's all
the definition we need.

regards, tom lane