Horton
Menu

Thread

  1. Conversion errors for datetime fields

    PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2000-12-28T15:55:52Z

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

  2. Re: Conversion errors for datetime fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-28T16:28:14Z

    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

  3. Re: Conversion errors for datetime fields

    Larry Rosenman <ler@lerctr.org> — 2000-12-28T17:16:25Z

    * 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

  4. Re: Conversion errors for datetime fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-28T17:33:07Z

    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

  5. Re: Conversion errors for datetime fields

    Larry Rosenman <ler@lerctr.org> — 2000-12-29T01:39:01Z

    * 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

  6. Re: Conversion errors for datetime fields

    Larry Rosenman <ler@lerctr.org> — 2000-12-29T02:37:32Z

    * 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

  7. Re: Conversion errors for datetime fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-29T02:44:06Z

    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

  8. Re: Conversion errors for datetime fields

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-12-29T04:00:14Z

    > 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

  9. Re: Conversion errors for datetime fields

    Larry Rosenman <ler@lerctr.org> — 2000-12-29T04:13:56Z

    * 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

  10. Re: Conversion errors for datetime fields

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-01-02T07:49:28Z

    > 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

  11. Re: Conversion errors for datetime fields

    Larry Rosenman <ler@lerctr.org> — 2001-01-02T12:38:26Z

    * 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