Skip site navigation (1) Skip section navigation (2)

Timestamp input + copy

From: "Kevin Bartz" <kbartz(at)loyaltymatrix(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp input + copy
Date: 2004-07-28 22:11:05
Message-ID: 20040728221644.A42F23FE8B@omta16.mta.everyone.net (view raw or flat)
Thread:
Lists: pgsql-general
Hi Postgressers! I really like Postgres. Thanks for all your work on it. I
just have a problem with the way it's handling my flat file's timestamp
columns.

I have a flat file with a column with dates formatted like this:

2004-04-15 18:04:26 PM

It's a bit strange, I know, but I didn't create the file. My idea of
Postgres's proper behavior would be to load this date as a military time
(and ignore the "PM"). MS SQL Server behaves in this way. Postgres, however,
won't even load the file:

bonusticket=copy bonusticket2004Q2 from
'/home/kevin/bonusticket/data3/uberfile/uberfile1.txt' null as '';
# ERROR:  date/time field value out of range: "2004-04-15 18:04:26 PM"
CONTEXT:  COPY bonusticket2004q2, line 17, column submit_date: "2004-04-15
18:04:26 PM"

I presume that Postgres is complaining about the fact that I have an 18 in
the hour slot of a supposedly PM time. What can I do about this? Can I
possibly specify a time format (similar to the 'YYYY-MM-DD HH24:MI:SS' I
might pass to to_timestamp) at load time?

Thanks for any help you can provide,

Kevin


Responses

pgsql-general by date

Next:From: Chris SmithDate: 2004-07-28 23:00:57
Subject: Re: Timestamp input + copy
Previous:From: Scott MarloweDate: 2004-07-28 21:31:20
Subject: Re: php -postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group