Thread: COPY returns Bad timestamp external rep..
Howdy, I'm trying to import data from an external database that has the date formatted as such: 2002-05-20 00.00.01. When I try to import the data, I get the Bad Timestamp external representation error. I would usually remove the time section, but for this particular table I need down to the second. Could someone point me in the right direction? TIA CREATE TABLE "xmlpricetest" ( "upc" int8 NOT NULL, "unit_price" float8, "compare_price" float8, "price_value" float8, "price_indicator" int2, "start_date" timestamp, "end_date" timestamp ) WITH OIDS; Patrick Hatcher Macys.Com
On Thu, 2002-05-23 at 17:40, Patrick Hatcher wrote: > Howdy, > I'm trying to import data from an external database that has the date > formatted as such: 2002-05-20 00.00.01. When I try to import the data, I > get the Bad Timestamp external representation error. I would usually > remove the time section, but for this particular table I need down to the > second. Could someone point me in the right direction? Do you know perl or python? awk might even be able to, say, convert all of the periods in the 6th & 7th fields to colons. > TIA > > > CREATE TABLE "xmlpricetest" ( > "upc" int8 NOT NULL, > "unit_price" float8, > "compare_price" float8, > "price_value" float8, > "price_indicator" int2, > "start_date" timestamp, > "end_date" timestamp > ) WITH OIDS; -- +---------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "I have created a government of whirled peas..." | | Maharishi Mahesh Yogi, 12-May-2002, | ! CNN, Larry King Live | +---------------------------------------------------------+
Stupid me. Thanks, your suggestion worked.
Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office
HatcherPT - AIM
Ron Johnson
<ron.l.johnson To: Patrick Hatcher <PHatcher@macys.com>
@cox.net> cc: PgSQL Novice ML <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] COPY returns Bad timestamp external rep..
05/23/2002
03:54 PM
On Thu, 2002-05-23 at 17:40, Patrick Hatcher wrote:
> Howdy,
> I'm trying to import data from an external database that has the date
> formatted as such: 2002-05-20 00.00.01. When I try to import the data, I
> get the Bad Timestamp external representation error. I would usually
> remove the time section, but for this particular table I need down to the
> second. Could someone point me in the right direction?
Do you know perl or python? awk might even be able to, say,
convert all of the periods in the 6th & 7th fields to colons.
> TIA
>
>
> CREATE TABLE "xmlpricetest" (
> "upc" int8 NOT NULL,
> "unit_price" float8,
> "compare_price" float8,
> "price_value" float8,
> "price_indicator" int2,
> "start_date" timestamp,
> "end_date" timestamp
> ) WITH OIDS;
--
+---------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://ronandheather.dhs.org:81 |
| |
| "I have created a government of whirled peas..." |
| Maharishi Mahesh Yogi, 12-May-2002, |
! CNN, Larry King Live |
+---------------------------------------------------------+
On Thu, 2002-05-23 at 18:29, Patrick Hatcher wrote: > > Stupid me. Thanks, your suggestion worked. So, which one worked? The scripting language, or awk? > Patrick Hatcher > Macys.Com > Legacy Integration Developer > 415-932-0610 office > HatcherPT - AIM > > > > > > Ron Johnson > <ron.l.johnson To: Patrick Hatcher <PHatcher@macys.com> > @cox.net> cc: PgSQL Novice ML <pgsql-novice@postgresql.org> > Subject: Re: [NOVICE] COPY returns Bad timestamp external rep.. > 05/23/2002 > 03:54 PM > > > > > > > On Thu, 2002-05-23 at 17:40, Patrick Hatcher wrote: > > Howdy, > > I'm trying to import data from an external database that has the date > > formatted as such: 2002-05-20 00.00.01. When I try to import the data, I > > get the Bad Timestamp external representation error. I would usually > > remove the time section, but for this particular table I need down to the > > second. Could someone point me in the right direction? > > Do you know perl or python? awk might even be able to, say, > convert all of the periods in the 6th & 7th fields to colons. > > > TIA > > > > > > CREATE TABLE "xmlpricetest" ( > > "upc" int8 NOT NULL, > > "unit_price" float8, > > "compare_price" float8, > > "price_value" float8, > > "price_indicator" int2, > > "start_date" timestamp, > > "end_date" timestamp > > ) WITH OIDS; > > -- > +---------------------------------------------------------+ > | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | > | Jefferson, LA USA http://ronandheather.dhs.org:81 | > | | > | "I have created a government of whirled peas..." | > | Maharishi Mahesh Yogi, 12-May-2002, | > ! CNN, Larry King Live | > +---------------------------------------------------------+ > > > > > -- +---------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "I have created a government of whirled peas..." | | Maharishi Mahesh Yogi, 12-May-2002, | ! CNN, Larry King Live | +---------------------------------------------------------+
used PERL
Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-932-0610 office
HatcherPT - AIM
Ron Johnson
<ron.l.johnson@cox.net> To: PgSQL Novice ML <pgsql-novice@postgresql.org>
Sent by: cc:
pgsql-novice-owner@post Subject: Re: [NOVICE] COPY returns Bad timestamp external rep..
gresql.org
05/23/2002 04:37 PM
On Thu, 2002-05-23 at 18:29, Patrick Hatcher wrote:
>
> Stupid me. Thanks, your suggestion worked.
So, which one worked? The scripting language, or awk?
> Patrick Hatcher
> Macys.Com
> Legacy Integration Developer
> 415-932-0610 office
> HatcherPT - AIM
>
>
>
>
>
> Ron Johnson
> <ron.l.johnson To: Patrick Hatcher
<PHatcher@macys.com>
> @cox.net> cc: PgSQL Novice ML
<pgsql-novice@postgresql.org>
> Subject: Re: [NOVICE] COPY
returns Bad timestamp external rep..
> 05/23/2002
> 03:54 PM
>
>
>
>
>
>
> On Thu, 2002-05-23 at 17:40, Patrick Hatcher wrote:
> > Howdy,
> > I'm trying to import data from an external database that has the date
> > formatted as such: 2002-05-20 00.00.01. When I try to import the data,
I
> > get the Bad Timestamp external representation error. I would usually
> > remove the time section, but for this particular table I need down to
the
> > second. Could someone point me in the right direction?
>
> Do you know perl or python? awk might even be able to, say,
> convert all of the periods in the 6th & 7th fields to colons.
>
> > TIA
> >
> >
> > CREATE TABLE "xmlpricetest" (
> > "upc" int8 NOT NULL,
> > "unit_price" float8,
> > "compare_price" float8,
> > "price_value" float8,
> > "price_indicator" int2,
> > "start_date" timestamp,
> > "end_date" timestamp
> > ) WITH OIDS;
>
> --
> +---------------------------------------------------------+
> | Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
> | Jefferson, LA USA http://ronandheather.dhs.org:81 |
> | |
> | "I have created a government of whirled peas..." |
> | Maharishi Mahesh Yogi, 12-May-2002, |
> ! CNN, Larry King Live |
> +---------------------------------------------------------+
>
>
>
>
>
--
+---------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://ronandheather.dhs.org:81 |
| |
| "I have created a government of whirled peas..." |
| Maharishi Mahesh Yogi, 12-May-2002, |
! CNN, Larry King Live |
+---------------------------------------------------------+
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org