Обсуждение: Error: Bad Timestamp Format
Hi all,
While moving a database installation from one machine to another, the
client app now fails with the following message:
java.rmi.RemoteException: Bad Timestamp Format at 19 in 2001-09-12
14:14:12.03-04; nested exception is:
According to the FAQ and docs, this is caused by the date format being
wrong, but the date format is the same on both installations:
vendirza=# SHOW DATESTYLE;
NOTICE: DateStyle is ISO with US (NonEuropean) conventions
vendirza=# SHOW DATESTYLE;
NOTICE: DateStyle is ISO with US (NonEuropean) conventions
I have checked the locale on both installations, and in both cases no
locale is set.
Can anyone suggest a solution?
I am using pgsql v7.0.3, and the database was transferred by doing a raw
transfer of the data/ directory from the first machine to the second.
Regards,
Graham
--
-----------------------------------------
minfrin@sharp.fm "There's a moon
over Bourbon Street
tonight..."
Вложения
On Wed, Sep 12, 2001 at 05:16:29PM +0200, Graham Leggett wrote: > According to the FAQ and docs, this is caused by the date format being > wrong, but the date format is the same on both installations: > > vendirza=# SHOW DATESTYLE; > NOTICE: DateStyle is ISO with US (NonEuropean) conventions > > vendirza=# SHOW DATESTYLE; > NOTICE: DateStyle is ISO with US (NonEuropean) conventions Looks the same machine to me. BTW, i don't know who thought of it but yyyy-dd-mm is a terrible format and should be shot. > I have checked the locale on both installations, and in both cases no > locale is set. > > Can anyone suggest a solution? Check the datestypes. Try setting it to european conventions, maybe that will work better > I am using pgsql v7.0.3, and the database was transferred by doing a raw > transfer of the data/ directory from the first machine to the second. Does pg_dump make a difference? HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
> BTW, i don't know who thought of it but yyyy-dd-mm is a terrible format and
> should be shot.
Where does yyyy-dd-mm appear in PostgreSQL? What is the issue here?
- Thomas
Martijn van Oosterhout wrote:
> > vendirza=# SHOW DATESTYLE;
> > NOTICE: DateStyle is ISO with US (NonEuropean) conventions
> >
> > vendirza=# SHOW DATESTYLE;
> > NOTICE: DateStyle is ISO with US (NonEuropean) conventions
>
> Looks the same machine to me.
That's because both machines are configured the same.
> BTW, i don't know who thought of it but yyyy-dd-mm is a terrible format and
> should be shot.
Yep.
> Check the datestypes. Try setting it to european conventions, maybe that
> will work better
I thought that is what I was doing with "SHOW DATETYPES".
What format is the date "2001-09-12 14:14:12.03-04"?
^^-- day
> Does pg_dump make a difference?
pg_dump seems to be useless - it doesn't dump the whole database (blobs
in the DB are missing).
Regards,
Graham
--
-----------------------------------------
minfrin@sharp.fm "There's a moon
over Bourbon Street
tonight..."
Вложения
Graham, I am guessing from the error that you are using JDBC as your client. This looks like a known issue with the 7.0 jdbc driver. Have your tried the 7.1 jdbc driver? (as long as you don't use the DatabaseMetadata object too much, the 7.1 driver should work fine against a 7.0 database). Also you might want to look at this message from the archives: http://archives.postgresql.org/pgsql-jdbc/2001-03/msg00168.php Which discusses this bug. thanks, --Barry Graham Leggett wrote: > Hi all, > > While moving a database installation from one machine to another, the > client app now fails with the following message: > > java.rmi.RemoteException: Bad Timestamp Format at 19 in 2001-09-12 > 14:14:12.03-04; nested exception is: > > According to the FAQ and docs, this is caused by the date format being > wrong, but the date format is the same on both installations: > > vendirza=# SHOW DATESTYLE; > NOTICE: DateStyle is ISO with US (NonEuropean) conventions > > vendirza=# SHOW DATESTYLE; > NOTICE: DateStyle is ISO with US (NonEuropean) conventions > > I have checked the locale on both installations, and in both cases no > locale is set. > > Can anyone suggest a solution? > > I am using pgsql v7.0.3, and the database was transferred by doing a raw > transfer of the data/ directory from the first machine to the second. > > Regards, > Graham > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl >
Barry Lind wrote: > I am guessing from the error that you are using JDBC as your client. > This looks like a known issue with the 7.0 jdbc driver. Have your tried > the 7.1 jdbc driver? (as long as you don't use the DatabaseMetadata > object too much, the 7.1 driver should work fine against a 7.0 database). > > Also you might want to look at this message from the archives: > http://archives.postgresql.org/pgsql-jdbc/2001-03/msg00168.php > Which discusses this bug. Thanks - I'll definitely check this out... Regards, Graham -- ----------------------------------------- minfrin@sharp.fm "There's a moon over Bourbon Street tonight..."
Вложения
On Wed, Sep 12, 2001 at 04:55:35PM +0000, Thomas Lockhart wrote: > > BTW, i don't know who thought of it but yyyy-dd-mm is a terrible format and > > should be shot. > > Where does yyyy-dd-mm appear in PostgreSQL? What is the issue here? From the original post: > vendirza=# SHOW DATESTYLE; > NOTICE: DateStyle is ISO with US (NonEuropean) conventions That's yyyy-dd-mm What you want is: # show datestyle; NOTICE: DateStyle is ISO with European conventions Which is yyyy-mm-dd HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
On Wed, Sep 12, 2001 at 06:55:50PM +0200, Graham Leggett wrote: > > Check the datestypes. Try setting it to european conventions, maybe that > > will work better > > I thought that is what I was doing with "SHOW DATETYPES". > > What format is the date "2001-09-12 14:14:12.03-04"? > ^^-- day The datestyle is two independant variables, watch: > show datestyle; NOTICE: DateStyle is ISO with European conventions > set datestyle=us; SET VARIABLE > show datestyle; NOTICE: DateStyle is ISO with US (NonEuropean) conventions > set datestyle=sql; SET VARIABLE > show datestyle; NOTICE: DateStyle is SQL with US (NonEuropean) conventions > set datestyle=european; SET VARIABLE > show datestyle; NOTICE: DateStyle is SQL with European conventions It affects the formatting of dates in various ways. Maybe it's fixed in recent versions but there was a time where ISO/non-US wouldn't parse 2001-09-30. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
Martijn van Oosterhout wrote:
>
> On Wed, Sep 12, 2001 at 04:55:35PM +0000, Thomas Lockhart wrote:
> > NOTICE: DateStyle is ISO with US (NonEuropean) conventions
> That's yyyy-dd-mm
Ah. No, that is not yyyy-dd-mm. It is yyyy-mm-dd (per ISO spec) for
output, and there are a wide range of various formats accepted for
input. The "US" vs "European" conventions come into play *only* when
trying to decipher ambiguous inputs which are *not* in ISO format.
The docs have a description of this, though suggestions (or patches) for
clarification are welcome.
hth
- Thomas