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

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

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

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

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

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

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

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