Thread: Re: [GENERAL] 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, Pennsylvania19026
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, Pennsylvania19026
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, Pennsylvania19026
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 |/