Re: Timestamp weirdness

Kevin Grittner wrote:

> If (hypothetically) timestampz is a protocol data element which does
> include time zone,

timestamptz is TIMESTAMP WITH TIME ZONE; it does include a timezone. The
server uses that timezone to determine the instant the timestamptz
represents when it parses the value.

> we might get to acceptable behavior if the JDBC
> driver always converted the timestamp representation to the time zone
> specified by the Calendar object and passed that time zone along.

This only works if the target type really is timestamptz.

If the target type is actually timestamp (TIMESTAMP WITHOUT TIME ZONE),
the server converts the instant identified by the timestamptz value
using the server's TimeZone setting to get a local date/time, and stores
that. This produces the wrong value if the timezone supplied by the
client isn't the same as the server TimeZone.

The JDBC driver doesn't know ahead of time whether the target type is
timestamp or timestamptz.

What I've suggested elsewhere is to invoke the server behaviour that
usually operates on untyped strings (by passing the "unknown" oid,
rather than either timestamp or timestamptz) when supplying type
information for parameters. The string -> timestamp conversion
*completely ignores* the supplied timezone, just using the specified
date/time directly, so we get the behaviour we want when the desired
type is really a timestamp. There are some disadvantages to this -- the
server doesn't necessarily always get it right, and it'd mask errors
such as using setTimestamp() on a text field -- but it seems the least
worst option.

-O