Importing from Access 2000?

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).

Options 1 or 2 are the way to go, but it's difficult to say more without
knowing what the data is supposed to mean. There is no year-zero, month-zero,
day-zero so they can't be timestamps. Let us know what they mean and someone
on the list will be able to suggest a suitable type, I'm sure.

HTH

--
Richard Huxton

Well, they are timestamps but they are just using the time part without
the date part in the old access db.
So I am not sure which type it should be the postgresql db, so any
suggestions are appreciated...

BTJ

On Tue, 2003-04-29 at 15:25, 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).

Options 1 or 2 are the way to go, but it's difficult to say more without
knowing what the data is supposed to mean. There is no year-zero, month-zero,
day-zero so they can't be timestamps. Let us know what they mean and someone
on the list will be able to suggest a suitable type, I'm sure.

HTH

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no Havleik Consulting
Phone : +47 67 54 15 17 Conradisvei 4
Fax : +47 67 54 13 91 N-1338 Sandvika
Cellular : +47 926 93 298 http://www.havleik.no
-----------------------------------------------------------------------------------------------
"The stickers on the side of the box said "Supported Platforms: Windows
98, Windows NT 4.0,
Windows 2000 or better", so clearly Linux was a supported platform."
-----------------------------------------------------------------------------------------------

On Tue, 2003-04-29 at 14:31, Bjørn T Johansen wrote:

Well, they are timestamps but they are just using the time part without
the date part in the old access db.
So I am not sure which type it should be the postgresql db, so any
suggestions are appreciated...

There is a TIME data type.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And he said to them all, If any man will come after
me, let him deny himself, and take up his cross daily,
and follow me." Luke 9:23

those are bad dates, what do you expect it to do?

Bjorn T Johansen wrote:

a timestamp is a point in real time, hence, the date is required. Intervals are an amount of time, and are probably what you need.

Bjørn T Johansen wrote:

On Tuesday 29 Apr 2003 2:46 pm, Oliver Elphick wrote:

On Tue, 2003-04-29 at 14:31, Bjørn T Johansen wrote:

Well, they are timestamps but they are just using the time part without
the date part in the old access db.
So I am not sure which type it should be the postgresql db, so any
suggestions are appreciated...

There is a TIME data type.

And if in fact they aren't times (e.g. 2pm) , but intervals (e.g. 2 hours 30
mins) then there is an "interval" type.

--
Richard Huxton

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;