Postgres Pro
Downloads
Home   >   mailing lists

Thread: Odd error in timestamp processing

Odd error in timestamp processing

From
Jeff Boes
Date:
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

Re: Odd error in timestamp processing

From
"Robert M. Meyer"
Date:
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

Re: Odd error in timestamp processing

From
Tim Ellis
Date:
> 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.

Re: Odd error in timestamp processing

From
"Robert M. Meyer"
Date:
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

CP-1251 encoding and ILIKE

From
Heni Lolov
Date:
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