Re: Bad timestamp external representation

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