Skip site navigation (1) Skip section navigation (2)

Re: Conversion errors for datetime fields

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Larry Rosenman <ler(at)lerctr(dot)org>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Conversion errors for datetime fields
Date: 2000-12-29 02:44:06
Message-ID: 9369.978057846@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2000-12-29 04:00:14
Subject: Re: Conversion errors for datetime fields
Previous:From: Larry RosenmanDate: 2000-12-29 02:37:32
Subject: Re: Conversion errors for datetime fields

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group