Thread: Importing from Access 2000?
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' ERROR: Bad timestamp external representation '0000-00-00 15:50:00' ERROR: Bad timestamp external representation '0000-00-00 17:20:00' ERROR: Bad timestamp external representation '0000-00-00 18:20:00' ERROR: Bad timestamp external representation '0000-00-00 18:30:00' ERROR: Bad timestamp external representation '0000-00-00 18:40:00' ERROR: Bad timestamp external representation '0000-00-00 18:50:00' ERROR: Bad timestamp external representation '0000-00-00 19:00:00' ERROR: Bad timestamp external representation '0000-00-00 23:00:00' ERROR: Bad timestamp external representation '0000-00-00 23:30:00' ERROR: Bad timestamp external representation '0000-00-00' ERROR: Bad timestamp external representation '0000-00-00 00:30:00' ERROR: Bad timestamp external representation '0000-00-00 01:00:00' 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) Regards, BTJ ----------------------------------------------------------------------------------------------- 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 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: > 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' > ERROR: Bad timestamp external representation '0000-00-00 15:50:00' > ERROR: Bad timestamp external representation '0000-00-00 17:20:00' > ERROR: Bad timestamp external representation '0000-00-00 18:20:00' > ERROR: Bad timestamp external representation '0000-00-00 18:30:00' > ERROR: Bad timestamp external representation '0000-00-00 18:40:00' > ERROR: Bad timestamp external representation '0000-00-00 18:50:00' > ERROR: Bad timestamp external representation '0000-00-00 19:00:00' > ERROR: Bad timestamp external representation '0000-00-00 23:00:00' > ERROR: Bad timestamp external representation '0000-00-00 23:30:00' > ERROR: Bad timestamp external representation '0000-00-00' > ERROR: Bad timestamp external representation '0000-00-00 00:30:00' > ERROR: Bad timestamp external representation '0000-00-00 01:00:00' > 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) > > Regards, > > BTJ > > > ----------------------------------------------------------------------------------------------- > 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." > ----------------------------------------------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
a timestamp is a point in real time, hence, the date is required. Intervals are an amount of time, and are probably whatyou need. 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... > > > 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
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;