Обсуждение: Conversion errors for datetime fields
Leonardo Frittelli (lfrittelli@tutopia.com) reports a bug with a severity of 3
The lower the number the more severe it is.
Short Description
Conversion errors for datetime fields
Long Description
I am currently using Postgresql version 7.0.2, but I did not find any reference to this problem in your bug/fix report
forrelease 7.0.3.
I have noticed some conversion errors while using datetime/time fields with decimal values.
Here I send you two situations that I have been able to isolate.
I think that both are rounding errors.
I found these problems in Posgresql version 6.5.1 also, but it had been corrected in release 6.5.2. Now it has somehow
reappeared.
Sample Code
-- Situation Nr 1
create table foo(
my_date datetime
);
insert into foo values (now()::date+'0:00:59.999999999999999'::time);
select my_date from foo;
-- Output from psql
--CREATE
--INSERT 90665 1
-- my_date
-----------------------------
-- 2000-12-28 00:01:60.00+00
--(1 row)
-- Note the '60 seconds' output
--------------------------------------------------------
-- Situation Nr 2
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)
-- Note that in the second case the result is rounded down instead of up
No file was uploaded with this report
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