Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    8

    Unhappy Unanswered: ERROR: Bad time external representation '134215'

    ERROR: Bad time external representation '134215'
    Hi,

    I have recently upgraded my postgres database to 7.3 and have had several problems with my data import.

    I currently am getting the message ERROR: Bad time external representation '134215' which I know does not recognise the time format but how can I update postgres to recognise this

    any help would be appreciated

    thanks

  2. #2
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    Well, it's really hard to help unless we can see a sample of the data that caused the error. But, it's obvious that somehow you are trying to import data that doesn't fit the time format.

    So, I don't think there is any way that you can "update postgres to recognise this", but rather you need to identify just where that imporperly-formatted data is coming from, and see how to fix it. It looks to me like you might have accidentally gotten columns in the wrong order, or something like that. Is it possible to see your table structure, and just one row from the dump that you are trying to restore?

  3. #3
    Join Date
    Aug 2003
    Posts
    8

    ERROR: Bad time external representation '134215'

    Hi,

    You do actually have a piece of the data, that is what the 134215 is.

    this is in the correct place but the postgres database does not seem to recognise this as a vaild time anymore this should represent 13:42:15
    hhmiss

    Not sure but digging through web pages it may be that postgres have now changed this to an eight bit format hh:mi:ss:ms ???

    thanks

  4. #4
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    OK, so we have one small fragment, and I don't know your table structure, or even the definition of that column, nor do we know which version of Postgres you are migrating from. When asking for help, it... helps to provide complete information.

    In the absence of that info, the best I can say is that every dump I have seen from a modern version of PostgreSQL used colons between the hours, minutes and seconds. Something like 12:19:52 will be recognized just fine by PostgreSQl 7.3, as well as the higher-precision style of 12:19:52.9868394, and ditto with full timestamps such as 2002-10-16 12:19:52.9868394. So, if PostgreSQL used to dump time data without colons, maybe they have discontinued support for that format. It should be easy enough to fix that data with a good text editor, or maybe a Perl script.

  5. #5
    Join Date
    Aug 2003
    Posts
    8
    hi,

    sorry yes the column is set to time and every data insert is in the format of '121212' HHMMSS no : between the data, this used to work with postgres 7.1 and now will not work with 7.3.2

    I think they may have stopped supporting this but can not find any information to support this. I know they have stopped supporting inserting fiels with a null value but managed to get round that with piping the file through sed command

    If I am requied to insert : between each value please advise of best way as there is about 95000 inserts per day each containing a time value in the above format 'hhmmss'

    thank you for your input

  6. #6
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    Originally posted by hughk
    If I am requied to insert : between each value please advise of best way as there is about 95000 inserts per day each containing a time value in the above format 'hhmmss'

    thank you for your input
    Oh, I thought you were just trying to update from a dump. If you have to accept inserts, then your best bet is to add some sort of trigger function or rule that converts the data to the correct format on insert. Of course, this will add a small processing overhead to every insert, but it should still be OK. (Let's see... 95000 inserts per day come to 1.09 inserts per second, or 3.3 inserts per second if you mean 8-hour days. I can't imagine that the trigger, if done right, would add more than a few milliseconds to the processing time, and PostgreSQL 7.3 has much better performance than 7.1 anyway).

    But, isn't there anyway you can just change the connecting application to insert in the correct format?

  7. #7
    Join Date
    Aug 2003
    Posts
    8
    Originally posted by rycamor
    Oh, I thought you were just trying to update from a dump. If you have to accept inserts, then your best bet is to add some sort of trigger function or rule that converts the data to the correct format on insert. Of course, this will add a small processing overhead to every insert, but it should still be OK. (Let's see... 95000 inserts per day come to 1.09 inserts per second, or 3.3 inserts per second if you mean 8-hour days. I can't imagine that the trigger, if done right, would add more than a few milliseconds to the processing time, and PostgreSQL 7.3 has much better performance than 7.1 anyway).

    But, isn't there anyway you can just change the connecting application to insert in the correct format?
    THANKYOU VERY MUCH FOR YOUR HELP BUT MANAGED TO FIND THE PROBLEM, THE NEW VERSION OF POSTGRES REQUIRES A t INFRONT OF THE TIME UPDATE...IE 't124364' IVE ADDED THIS INTO THE INSERT SCRIPT AND IT IS WORKING

    MANY THANKS AGAIN FOR YOU HELP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •