Conversion errors for datetime fields

pgsql-bugs@postgresql.org writes:

select '0:00:59.99999999999999'::time as fourteen_dec,
'0:00:59.999999999999999'::time as fifteen_dec;

-- Output from psql
-- fourteen_dec | fifteen_dec
----------------+-------------
-- 00:00:59 | 00:00:00
--(1 row)

What I'm getting with current sources is

regression=# select '0:00:59.99999999999999'::time as fourteen_dec;
fourteen_dec
--------------
00:00:59
(1 row)

which seems to be rounding in the wrong direction, and

regression=# select '0:00:59.999999999999999'::time as fifteen_dec;
ERROR: Bad time external representation '0:00:59.999999999999999'

That one seems reasonable, since this input is indistinguishable from

regression=# select '0:00:60'::time;
ERROR: Bad time external representation '0:00:60'

However, I also get

regression=# select now()::date+'0:00:59.999'::time;
?column?
---------------------------
2000-12-28 00:00:60.00-05
(1 row)

The cause is clear enough: the 59.999 seconds are being rounded off
to two digits for display. But it's unfortunate that this causes a
displayed output that will not be accepted as valid input. Perhaps
it would be a good idea to round off the seconds to display precision
*before* the value is broken down to hh/mm/ss.

regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [001228 10:28]:

pgsql-bugs@postgresql.org writes:
regression=# select now()::date+'0:00:59.999'::time;
?column?
---------------------------
2000-12-28 00:00:60.00-05
(1 row)

The cause is clear enough: the 59.999 seconds are being rounded off
to two digits for display. But it's unfortunate that this causes a
displayed output that will not be accepted as valid input. Perhaps
it would be a good idea to round off the seconds to display precision
*before* the value is broken down to hh/mm/ss.

Aren't we *REQUIRED* by SQL99 to accept up to :61 to account for
leap seconds?

--
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

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.

regards, tom lane

* 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

* Larry Rosenman <ler@lerctr.org> [001228 19:39]:

* 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?

Oh, and the UnixWare strftime man page allows %s to return 00-61.

SO, we need to allow it as well. I suspect the C99 standard or
some other POSIX/SUS/etc standard changed.

So, we need to change.

LER

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

--
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

Larry Rosenman <ler@lerctr.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

Oh, and the UnixWare strftime man page allows %s to return 00-61.

They're just repeating a common mistake. If you want to learn something
about the subject, try some non-computer timekeeping references, for
example the US Naval Observatory:

http://tycho.usno.navy.mil/leapsec.html

After digging around a little, the source of this particular meme
seems to be the old C standard. The theory among those who are
aware it's an error is that some member of the ISO C committee did
enough research to know that two leap seconds could be inserted
in a single year, but not enough to realize that they wouldn't be
inserted in the same minute. (See above USNO page: in fact there
are four agreed-on windows for leap second insertion per year,
but only two have been used historically.) The error has since
contaminated the Java spec, as well as most all Unix documentation.

I suspect that this error may go back even further, perhaps to the
original Unix C library documentation. In any case it was only a
documentation error, as no C library of that vintage actually had
any leap-second support whatever.

SO, we need to allow it as well. I suspect the C99 standard or
some other POSIX/SUS/etc standard changed.

C99 *corrects* this error; it specifies 0-60 not 0-61 as the range
of tm_sec. (It also describes actual support for leap-second
timekeeping, which the original C standard did not.)

But this is all irrelevant, anyway, unless you want people to install
atomic clocks before they can run Postgres. We don't have support for
leap-second timekeeping, and few if any of the platforms we run on
do either. IMHO, accepting :60 when we do not have the ability to do
anything correct with it won't improve matters.

regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [001228 22:01]:

SO, we need to allow it as well. I suspect the C99 standard or
some other POSIX/SUS/etc standard changed.

C99 *corrects* this error; it specifies 0-60 not 0-61 as the range
of tm_sec. (It also describes actual support for leap-second
timekeeping, which the original C standard did not.)

But this is all irrelevant, anyway, unless you want people to install
atomic clocks before they can run Postgres. We don't have support for
leap-second timekeeping, and few if any of the platforms we run on
do either. IMHO, accepting :60 when we do not have the ability to do
anything correct with it won't improve matters.

regards, tom lane

Ok. I just wanted to mention what I had thought was an
*Authoritative* source.

Thanks for your research time.....

LER

--
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

Looking at Page 166 of "SQL-99 Complete, Really" by Peter Gulutzan &
Trudy Peltzer, R&D Books, ISBN 0-87930-568-1, 1st bullet:

That is a strange name for a book. :-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

* Bruce Momjian <pgman@candle.pha.pa.us> [010102 01:49]:

Looking at Page 166 of "SQL-99 Complete, Really" by Peter Gulutzan &
Trudy Peltzer, R&D Books, ISBN 0-87930-568-1, 1st bullet:

That is a strange name for a book. :-)

They explain the title as meaning they don't refer you off to another
book for SQL stuff. It's actually a nice book. However, given the
discussion Tom and I had on-list, I really need to get a REAL copy of
the standard someday.

LER

--
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