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(at)bryncadfan(dot)co(dot)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(at)zembu(dot)com