Hi,

Just created a db from a pg_dump file and got this error:

ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
09:01:60.00+00'

I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
but how could it have got into my database/can I do anything about it?  The
value must have been inserted by my app via JDBC, I can't insert that value
directly via psql.

Thanks,
Tamsin

                               version
---------------------------------------------------------------------
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)


Tue, 24 Jul 2001 10:15:42 +0100
"tamsin" <tg_mail@bryncadfan.co.uk>
Bad timestamp external representation
From: "tamsin" <tg_mail@bryncadfan.co.uk>

> Hi,
>
> Just created a db from a pg_dump file and got this error:
>
> ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
> 09:01:60.00+00'
>
> I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
> but how could it have got into my database/can I do anything about it?
The
> value must have been inserted by my app via JDBC, I can't insert that
value
> directly via psql.

Seem to remember a bug in either pg_dump or timestamp rendering causing
rounding-up problems like this. If no-one else comes up with a definitive
answer, check the list archives. If you're not running the latest release,
check the change-log.

HTH

- Richard Huxton


Tue, 24 Jul 2001 13:58:29 +0100
"Richard Huxton" <dev@archonet.com>
Re: Bad timestamp external representation
It's a bug in timestamp output.

# select '2001-07-24 15:55:59.999'::timestamp;
         ?column?          
---------------------------
 2001-07-24 15:55:60.00-04
(1 row)

Richard Huxton wrote:
> 
> From: "tamsin" <tg_mail@bryncadfan.co.uk>
> 
> > Hi,
> >
> > Just created a db from a pg_dump file and got this error:
> >
> > ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
> > 09:01:60.00+00'
> >
> > I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
> > but how could it have got into my database/can I do anything about it?
> The
> > value must have been inserted by my app via JDBC, I can't insert that
> value
> > directly via psql.
> 
> Seem to remember a bug in either pg_dump or timestamp rendering causing
> rounding-up problems like this. If no-one else comes up with a definitive
> answer, check the list archives. If you're not running the latest release,
> check the change-log.
> 
> HTH
> 
> - Richard Huxton
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Tue, 24 Jul 2001 15:57:00 -0400
Joseph Shraibman <jks@selectacast.net>
Re: Bad timestamp external representation

I can confirm that current CVS sources have the same bug.

> It's a bug in timestamp output.
> 
> # select '2001-07-24 15:55:59.999'::timestamp;
>          ?column?          
> ---------------------------
>  2001-07-24 15:55:60.00-04
> (1 row)
> 
> Richard Huxton wrote:
> > 
> > From: "tamsin" <tg_mail@bryncadfan.co.uk>
> > 
> > > Hi,
> > >
> > > Just created a db from a pg_dump file and got this error:
> > >
> > > ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
> > > 09:01:60.00+00'
> > >
> > > I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
> > > but how could it have got into my database/can I do anything about it?
> > The
> > > value must have been inserted by my app via JDBC, I can't insert that
> > value
> > > directly via psql.
> > 
> > Seem to remember a bug in either pg_dump or timestamp rendering causing
> > rounding-up problems like this. If no-one else comes up with a definitive
> > answer, check the list archives. If you're not running the latest release,
> > check the change-log.
> > 
> > HTH
> > 
> > - Richard Huxton
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> 
> -- 
> Joseph Shraibman
> jks@selectacast.net
> Increase signal to noise ratio.  http://www.targabot.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 

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

Wed, 25 Jul 2001 18:53:21 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [GENERAL] Bad timestamp external representation
On Wed, Jul 25, 2001 at 06:53:21PM -0400, Bruce Momjian wrote:
> 
> I can confirm that current CVS sources have the same bug.
> 
> > It's a bug in timestamp output.
> > 
> > # select '2001-07-24 15:55:59.999'::timestamp;
> >          ?column?          
> > ---------------------------
> >  2001-07-24 15:55:60.00-04
> > (1 row)
> > 
> > Richard Huxton wrote:
> > > 
> > > From: "tamsin" <tg_mail@bryncadfan.co.uk>
> > > 
> > > > Hi,
> > > >
> > > > Just created a db from a pg_dump file and got this error:
> > > >
> > > > ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
> > > > 09:01:60.00+00'
> > > >
> > > > I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
> > > > but how could it have got into my database/can I do anything about it?
> > > The
> > > > value must have been inserted by my app via JDBC, I can't insert that
> > > value
> > > > directly via psql.
> > > 
> > > Seem to remember a bug in either pg_dump or timestamp rendering causing
> > > rounding-up problems like this. If no-one else comes up with a definitive
> > > answer, check the list archives. If you're not running the latest release,
> > > check the change-log.

It is not a bug, in general, to generate or accept times like 09:01:60.  
Leap seconds are inserted as the 60th second of a minute.  ANSI C 
defines the range of struct member tm.tm_sec as "seconds after the 
minute [0-61]", inclusive, and strftime format %S as "the second
as a decimal number (00-61)".  A footnote mentions "the range [0-61]
for tm_sec allows for as many as two leap seconds".

This is not to say that pg_dump should misrepresent stored times,
but rather that PG should not reject those misrepresented times as 
being ill-formed.  We were lucky that PG has the bug which causes
it to reject these times, as it led to the other bug in pg_dump being
noticed.

Nathan Myers
ncm@zembu.com

Wed, 25 Jul 2001 16:31:31 -0700
ncm@zembu.com (Nathan Myers)
Re: Bad timestamp external representation
> On Wed, Jul 25, 2001 at 06:53:21PM -0400, Bruce Momjian wrote:
> > 
> > I can confirm that current CVS sources have the same bug.
> > 
> > > It's a bug in timestamp output.
> > > 
> > > # select '2001-07-24 15:55:59.999'::timestamp;
> > >          ?column?          
> > > ---------------------------
> > >  2001-07-24 15:55:60.00-04
> > > (1 row)
> > > 
> > > Richard Huxton wrote:
> > > > 
> > > > From: "tamsin" <tg_mail@bryncadfan.co.uk>
> > > > 
> > > > > Hi,
> > > > >
> > > > > Just created a db from a pg_dump file and got this error:
> > > > >
> > > > > ERROR:  copy: line 602, Bad timestamp external representation '2000-10-03
> > > > > 09:01:60.00+00'
> > > > >
> > > > > I guess its a bad representation because 09:01:60.00+00 is actually 09:02,
> > > > > but how could it have got into my database/can I do anything about it?
> > > > The
> > > > > value must have been inserted by my app via JDBC, I can't insert that
> > > > value
> > > > > directly via psql.
> > > > 
> > > > Seem to remember a bug in either pg_dump or timestamp rendering causing
> > > > rounding-up problems like this. If no-one else comes up with a definitive
> > > > answer, check the list archives. If you're not running the latest release,
> > > > check the change-log.
> 
> It is not a bug, in general, to generate or accept times like 09:01:60.  
> Leap seconds are inserted as the 60th second of a minute.  ANSI C 
> defines the range of struct member tm.tm_sec as "seconds after the 
> minute [0-61]", inclusive, and strftime format %S as "the second
> as a decimal number (00-61)".  A footnote mentions "the range [0-61]
> for tm_sec allows for as many as two leap seconds".
> 
> This is not to say that pg_dump should misrepresent stored times,
> but rather that PG should not reject those misrepresented times as 
> being ill-formed.  We were lucky that PG has the bug which causes
> it to reject these times, as it led to the other bug in pg_dump being
> noticed.

We should access :60 seconds but we should round 59.99 to 1:00, right?

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

Thu, 26 Jul 2001 17:38:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bad timestamp external representation
On Thu, Jul 26, 2001 at 05:38:23PM -0400, Bruce Momjian wrote:
> Nathan Myers wrote:
> > Bruce wrote:
> > > 
> > > I can confirm that current CVS sources have the same bug.
> > > 
> > > > It's a bug in timestamp output.
> > > > 
> > > > # select '2001-07-24 15:55:59.999'::timestamp;
> > > >          ?column?          
> > > > ---------------------------
> > > >  2001-07-24 15:55:60.00-04
> > > > (1 row)
> > > > 
> > > > Richard Huxton wrote:
> > > > > 
> > > > > From: "tamsin" <tg_mail@bryncadfan.co.uk>
> > > > > 
> > > > > > Hi,
> > > > > >
> > > > > > Just created a db from a pg_dump file and got this error:
> > > > > >
> > > > > > ERROR:  copy: line 602, Bad timestamp external representation 
> > > > > > '2000-10-03 09:01:60.00+00'
> > > > > >
> > > > > > I guess its a bad representation because 09:01:60.00+00
> > > > > > is actually 09:02, but how could it have got into my
> > > > > > database/can I do anything about it? The value must have
> > > > > > been inserted by my app via JDBC, I can't insert that value
> > > > > > directly via psql.
> > > > >
> > > > > Seem to remember a bug in either pg_dump or timestamp
> > > > > rendering causing rounding-up problems like this. If no-one
> > > > > else comes up with a definitive answer, check the list
> > > > > archives. If you're not running the latest release, check the
> > > > > change-log.
> >
> > It is not a bug, in general, to generate or accept times like
> > 09:01:60. Leap seconds are inserted as the 60th second of a minute.
> > ANSI C defines the range of struct member tm.tm_sec as "seconds
> > after the minute [0-61]", inclusive, and strftime format %S as "the
> > second as a decimal number (00-61)". A footnote mentions "the range
> > [0-61] for tm_sec allows for as many as two leap seconds".
> >
> > This is not to say that pg_dump should misrepresent stored times,
> > but rather that PG should not reject those misrepresented times as
> > being ill-formed. We were lucky that PG has the bug which causes it
> > to reject these times, as it led to the other bug in pg_dump being
> > noticed.
>
> We should access :60 seconds but we should round 59.99 to 1:00, right?

If the xx:59.999 occurred immediately before a leap second, rounding it
up to (xx+1):00.00 would introduce an error of 1.001 seconds.

As I understand it, the problem is in trying to round 59.999 to two
digits.  My question is, why is pg_dump representing times with less 
precision than PostgreSQL's internal format?  Should pg_dump be lossy?

Nathan Myers
ncm@zembu.com

Thu, 26 Jul 2001 15:13:34 -0700
ncm@zembu.com (Nathan Myers)
Re: Bad timestamp external representation
> > > It is not a bug, in general, to generate or accept times like
> > > 09:01:60. Leap seconds are inserted as the 60th second of a minute.
> > > ANSI C defines the range of struct member tm.tm_sec as "seconds
> > > after the minute [0-61]", inclusive, and strftime format %S as "the
> > > second as a decimal number (00-61)". A footnote mentions "the range
> > > [0-61] for tm_sec allows for as many as two leap seconds".
> > >
> > > This is not to say that pg_dump should misrepresent stored times,
> > > but rather that PG should not reject those misrepresented times as
> > > being ill-formed. We were lucky that PG has the bug which causes it
> > > to reject these times, as it led to the other bug in pg_dump being
> > > noticed.
> >
> > We should access :60 seconds but we should round 59.99 to 1:00, right?
> 
> If the xx:59.999 occurred immediately before a leap second, rounding it
> up to (xx+1):00.00 would introduce an error of 1.001 seconds.

Oh, so there is a good reason for showing :60.

> As I understand it, the problem is in trying to round 59.999 to two
> digits.  My question is, why is pg_dump representing times with less 
> precision than PostgreSQL's internal format?  Should pg_dump be lossy?

No idea.

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

Thu, 26 Jul 2001 19:42:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bad timestamp external representation
At 15:13 26/07/01 -0700, Nathan Myers wrote:
>Should pg_dump be lossy?

No it shouldn't, but it already is because it uses decimal text reps of
everything; we lose data when dumping floats as well. In the latter case we
should dump the hex text reps to get the full bit width. Something similar
is probably true for times etc. It's just a lot less readable.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Fri, 27 Jul 2001 12:18:56 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Bad timestamp external representation