Обсуждение: Odd error in timestamp processing
Recently we transferred our database from a 7.1 system to a 7.2 using: $ pg_dump -h old_host | psql During this, we saw this message: ERROR: copy: line 15926, Bad timestamp external representation '2002-07-21 06:54:60.00-04' (It's the '60 seconds' part of the timestamp that is causing the problem, I'm sure.) How might this have happened? Some kind of rounding error? -- Jeff Boes vox 616.226.9550 ext 24 Database Engineer fax 616.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise
We had this problem, too. Very annoying. It appears to be a bug in 7.1.x. The way we got around it was to dump the DB out to a file and run sed on the file with the script: s/:60/:59/ Then feeding that output into psql. Of course, our database doesn't use ':' followed by numbers for anything but timestamps so your mileage may vary. Hope this helps... Cheers! Bob On Tue, 2002-07-23 at 14:14, Jeff Boes wrote: > Recently we transferred our database from a 7.1 system to a 7.2 using: > > $ pg_dump -h old_host | psql > > During this, we saw this message: > > > ERROR: copy: line 15926, Bad timestamp external representation > '2002-07-21 06:54:60.00-04' > > (It's the '60 seconds' part of the timestamp that is causing the problem, > I'm sure.) How might this have happened? Some kind of rounding error? > > -- > Jeff Boes vox 616.226.9550 ext 24 > Database Engineer fax 616.349.9076 > Nexcerpt, Inc. http://www.nexcerpt.com > ...Nexcerpt... Extend your Expertise > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Robert M. Meyer Sr. Network Administrator DigiVision Satellite Services 14 Lafayette Sq, Ste 410 Buffalo, NY 14203-1904 (716)332-1451
> We had this problem, too. Very annoying. It appears to be a bug in
> 7.1.x. The way we got around it was to dump the DB out to a file and
> run sed on the file with the script:
> s/:60/:59/
>
> Then feeding that output into psql.
>
> Of course, our database doesn't use ':' followed by numbers for anything
> but timestamps so your mileage may vary.
Not knowing your dataset, it's safer to run it through a Perl script
using:
if ($line =~ /^(.*?)(\d\d:\d\d:)60(\.\d\d-\d\d.*)$/) {
$newLine = $1 . $2 . "59" . $3;
} else {
$newLine = $line;
}
This pretty much makes sure that any ":60" you get is within a string like
**:**:60:**-** where each * is a 0-9.
That, or use awk as well as sed to make sure you get the proper :60
replaced.
In any case, I wouldn't trust my large dataset to contain only improper
:60's. I've been bitten too many times by a too-generic s/XYZ/ABC/ in
the past.
--
Tim Ellis
Senior Database Architect
Gamet, Inc.
You're right on that. I actually grepped my database dump to find all
occurrences of ':60' and only found three dates and no other matches.
Since that was the case, I went with the simple sed script (I was pretty
tired at that point) and it worked for me. I was about to copy out your
script, but I remembered that all of my databases have been upgraded so
I don't need to worry about that any more. I like the script, though.
Cheers!
Bob
On Tue, 2002-07-23 at 17:25, Tim Ellis wrote:
> > We had this problem, too. Very annoying. It appears to be a bug in
> > 7.1.x. The way we got around it was to dump the DB out to a file and
> > run sed on the file with the script:
> > s/:60/:59/
> >
> > Then feeding that output into psql.
> >
> > Of course, our database doesn't use ':' followed by numbers for anything
> > but timestamps so your mileage may vary.
>
> Not knowing your dataset, it's safer to run it through a Perl script
> using:
>
> if ($line =~ /^(.*?)(\d\d:\d\d:)60(\.\d\d-\d\d.*)$/) {
> $newLine = $1 . $2 . "59" . $3;
> } else {
> $newLine = $line;
> }
>
> This pretty much makes sure that any ":60" you get is within a string like
> **:**:60:**-** where each * is a 0-9.
>
> That, or use awk as well as sed to make sure you get the proper :60
> replaced.
>
> In any case, I wouldn't trust my large dataset to contain only improper
> :60's. I've been bitten too many times by a too-generic s/XYZ/ABC/ in
> the past.
>
> --
> Tim Ellis
> Senior Database Architect
> Gamet, Inc.
>
> ---------------------------(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
--
Robert M. Meyer
Sr. Network Administrator
DigiVision Satellite Services
14 Lafayette Sq, Ste 410
Buffalo, NY 14203-1904
(716)332-1451
Hi, I have problem that I can not solve. How to use CP-1251 encoding and to be able to make case insensitive selects with ILIKE ? It is urgent! Thanks in advance! Hal __________________________________________________ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com