Re: Timestamp weirdness

emergency(dot)shower(at)gmail(dot)com wrote:

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

Have you read the long and involved threads that started a few days ago
on pgsql-jdbc? It's worth reading them in the archives if you haven't
already.

Basically, these problems are known about and we're trying to hash out a
reasonable solution..

> Here's the behaviour that I would expect:
>
> 1) The values in the database should not depend on the database's
> local time zone.

Since WITH TIME ZONE <-> WITHOUT TIME ZONE conversions use the server's
TimeZone setting, this requires that there are no implicit conversions
between the two happening on the setTimestamp() / getTimestamp() paths.

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

(3 and 5: ... or the JVM's default Calendar if none is specified)

This is also the behaviour I'd want, but I don't think I have 100%
agreement on that from everyone yet.

-O