Timestamp weirdness - Mailing list pgsql-jdbc

Hi,

With PostgreSQL 8.0.3 and the postgresql-jdbc-8.1dev-400
JDBC driver, there are a number of problems when writing timestamps to
and reading them from the database.

First of all, I would expect
 PreparedStatemant#setTimestamp("fld", Timestamp, Calendar)
and
 ResultSet#getTimestamp("fld", Calendar)
to be complementary methods.

When writing a java.sql.Timestamp to a TIMESTAMP WITHOUT TIME ZONE or
to a TIMESTAMP WITH TIME ZONE database field and then reading it using
the same java.util.Calendar should return the same Timestamp value that
was previously written to the database.

However, if

 final java.sql Timestamp ts;
 final java.util.Calendar cal;

 ts = new Timestamp(0L);
 ts.setNanos(0);
 cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

then writing with

 PreparedStatemant#setTimestamp("fld", ts, cal)

and reading with

 ResultSet#getTimestamp("fld", Calendar)

will give a result that differs from the original value by the
server's local time zone offset if "fld" is TIMESTAMP WITHOUT TIME
ZONE.

When reading/writing to a TIMESTAMP WITH TIME ZONE the correctness of
the result depends on the server's time zone. E.g. if the server has
the Newfoundland time zone the result differs from the correct one by
30 minutes.

If we use a Calendar other than UTC, with a non-zero time zone offset,
the results are generally incorrect and difficult to predict. They
seem to depend on

- the database servers local time zone,
- the the Calendar's time zone offset,
- the database field's WITH or WITHOUT TIME ZONE attribute,
- and rounding errors if the Calendar's time zone offset contains
fractions of hours.


Here's the behaviour that I would expect:

1) The values in the database should not depend on the database's
local time zone.

2) When writing to a TIMESTAMP WITH TIME ZONE field, the driver should
not perform any time zone conversions and should store the Timestamp's
(UTC) y, M, d, H, m, s values directly to the database. The Calendar,
if given, should be ignored.

3) When writing to a TIMESTAMP WITHOUT TIME ZONE field, the driver
should calculate the Timestamp's y, M, d, H, m, s values in the given
Calendar's time zone and should store these values in the database.

4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
should create a Timestamp by interpreting the y, M, d, H, m, s values
as UTC timestamp fields. The Calendar, if given, should be ignored.

5) When reading from a TIMESTAMP WITHOUT TIME ZONE field, the driver
should create a Timestamp by interpreting the y, M, d, H, m, s values
in the context of the given Calendar.


Does this make sense?


Regards,
Alex