Re: Importing from Access 2000?

On Tue, 29 Apr 2003, Richard Huxton wrote:

> On Tuesday 29 Apr 2003 12:42 pm, Bjorn T Johansen wrote:
> > I am trying to convert an Access database to PostgreSQL and the import
> > works until it comes to timestamps, then I get error msg like these:
> >
> > ERROR:  Bad timestamp external representation '0000-00-00 01:00:00'
> [snip more errors]
> > ERROR:  Bad timestamp external representation '0000-00-00 19:00:00'
> >
> > Does anyone have any suggestion on how to overcome this problem?
> > (BTW, I am using PostgreSQL 7.3.2 and ODBC)
>
> These don't appear to be valid timestamps, hence the problem. At a guess,
> they're not actually being used to store timestamps. AFAICT your options are:
>
> 1. Fix the data so that they are valid timestamps.
> 2. Use some other format more suited for your data (interval?)
> 3. Store your timestamp values in text in PGSQL (no, I don't like this
> either).

Actually, I'd go with 3 as a temporary solution, then select the data into
another table with substr or something.

i.e.

select
substr(baddatefield,11,8)::interval as gooddatefield,
field2,
field3,
etc
into newtable
from oldandbusted;