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

Re: Bad timestamp external representation

From: miles <miles(at)faithwestinc(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Bad timestamp external representation
Date: 2004-01-12 20:38:55
Message-ID: 400305DF.4070502@faithwestinc.com (view raw or flat)
Thread:
Lists: sfpug
thanks for the reply, and the welcome, Josh.

our dumps are in fact generated with pg_dump on solaris 8 machines which 
is why we're confused... we noticed several date fields causing the 
problem in the last few weeks, for example:

2004-01-04 24:00:00
2003-12-09 16:06:63.9999999999-05
2003-07-19 16:54:127.9999999998-04
2003-03-17 00:00:60.0000000002-05

we manually edited the dumps and rectified these errors to keep moving, 
but I was trying to find the root cause

strangely if I select a record using psql on the server, I get this date 
2002-08-01 00:00:00, but looking at the same record in the pg_dump I see 
2002-07-31 24:00:00

Im trying to determine how this data was entered for all cases. In the 
24:00:00 case I know that current_date() was used to update a 
timestamptz field... Im sure there must be something in our process that 
changed recently to cause this, so Im looking into all cases

thanks in advance for any clues you may have

miles



Josh Berkus wrote:

>Miles,
>
>  
>
>>were experiencing some problems when restoring certain databases that
>>were dumped with pg_dump, where the date inserted using now() has some
>>strange rounding applied ... (postgres version is 7.2)
>>
>>ERROR:  copy: line 2, Bad timestamp external representation '2002-07-31
>>24:00:00'
>>    
>>
>
>Howdy and welcome to SFPUG!   I'm glad you've joined us; I have dreams of 
>someday getting your employer to do a case study.
>
>Correcting the dump file is easy; just do a search-and-replace (prefereably 
>with sed or Perl) for 24:00:00 and replace it with 00:00:00 , which is the 
>accepted format.
>
>I'm very curious how the dump file was generated, though.   PostgreSQL does 
>not accept 24:00 as a valid time, and pg_dump certainly wouldn't generate it.
>
>  
>


In response to

sfpug by date

Next:From: Craig AddlemanDate: 2004-01-13 18:05:26
Subject: Re: Meetings this month (January)
Previous:From: Josh BerkusDate: 2004-01-12 17:45:56
Subject: Re: Bad timestamp external representation

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