Thread: Jdbc : DateStyle problem

Hi,

I have a weird problem with jdbc PostGreSql driver 3
(postgresql-8.2-505.jdbc3.jar) and the DateStyle parameter on a
database.

If I set the following value by psql (ALTER DATABASE "myDatabase" SET
DateStyle =ISO,European), the style of date is correctly modified on the
database, even if i reconnect pgAdmin by example.
The request "show DateStyle" gives me a correct result : ISO, DMY

But, if i launch the same request by the jdbc driver (I work with
Eclipse and Sql Explorer plugins), i obtain the following result :
ISO,MDY.
Then, if i run a request, by jdbc, like : select * from table where
table.datefield = '15/01/2007' i have no result because postgresql wants
a MDY (01/15/2007) date. The same request, launched by psql, gives me
correct results.

The only way that i find is to run firstly (SET DATESTYLE TO
ISO,European;) on my jdbc session and then "show DateStyle" gives me :
ISO, DMY.
But, if i close my jdbc session, i lost my datestyle and "show
datestyle" returns ISO,DMY.
It's normal because the modification is set on the session only.
An another workaround is to set the DateStyle on the postgresql.conf but
all databases are impacted... Not really good.

But,why the modification of the datestyle, set by psql (alter
database....), on my database is not effective on a jdbc session?

Thanks in advance,

Regards.

Laurent Savary
SA MEDIAL
Lyon - France

Στις Παρασκευή 07 Σεπτέμβριος 2007 11:05, ο/η Laurent LS. Savary έγραψε:
> Hi,
>
> I have a weird problem with jdbc PostGreSql driver 3
[snip]

Since you are working with java it is useless to try to format/parse
dates/timestamps with DateStyle.
Setting DateStyle is great maybe for dba scripts/reports/etc..
but for your case, just use java.text.SimpleDateFormat for both
parsing/printing.

--
Achilleas Mantzios

Laurent LS. Savary wrote:

> But,why the modification of the datestyle, set by psql (alter
> database....), on my database is not effective on a jdbc session?

The JDBC driver resets DateStyle to a known value on each connection so
that it knows how to correctly format date values sent to the backend,
and correctly parse date values returned by the backend.

Really you should be using the JDBC methods that deal in terms of
java.sql.Date if you are manipulating dates (i.e.
PreparedStatement.setDate(), ResultSet.getDate() and so on). The driver
will then correctly handle the date value interpretation for you.

-O