Re: Timestamp without Timezone and differing client / server tzs - Mailing list pgsql-jdbc

Ken Johanson wrote:

>
> The servers and their schema (use-of ts w/o tz are not under my control; adding with-timezone would likely break apps
thattransmit date values with offset already normalized) 

This is the root of your problems, you're trying to use WITHOUT TIMEZONE
to represent an instant in time which only makes sense when you're using
the same timezone absolutely everywhere - and you're not. (Basically,
you've got an implicit extra bit of data floating around - the server
timezone - that's not correctly tied to your column data)

But if you can't change the schema, you can certainly work around it in
your application:

> Just to pass-in a timestamp, and which ever database it is sent to, be
> stored with its real atomic (integer) value (ie string conversion
> normalized to what db uses).
>
> Simplified:
>
> Timestamp NOW = new Timestamp(System.currentTimeMillis());
> for (int i=0; i<SERVERS.length; i++)
> {
> Connection con = ....//SERVERS[i] etc; each server exists in different
> timezone, and datetime/timestamps cols do not store zone.
> PreparedStatement ps = con.prepareStatement("INSERT INTO tbl
> (lastModified) VALUES (?)");
> ps.setTimestamp(1, NOW));
> ps.executeUpdate();
> }

Ok, so essentially you want to pass a timestamp-with-timezone value to
the server, then store it as timestamp-without-timezone, using the
server's timezone to do the cast, right?

What is biting you here is the server-side inference of the datatype of
your parameter. To support both WITH TIMEZONE and WITHOUT TIMEZONE via
setTimestamp(), the driver has to pass the parameter with an unknown
type and let the server infer the actual type.

For example if a client sets a timestamp of "05:00 +1000" (either via an
explicit Calendar, or because their default timezone is +1000) to insert
into a WITHOUT TIMEZONE column, the only sensible result is to insert
that as "05:00" regardless of the server's timezone setting. The client
sees the value as 05:00, so the only sensible thing the driver can do is
to insert it as 05:00.

The driver takes advantage of the fact that literals interpreted as
WITHOUT TIMEZONE completely ignore the timezone specification in this
case (and when inserting into a WITHOUT TIMEZONE column, the type of an
unknown-type parameter is inferred to be WITHOUT TIMEZONE).

If the driver passed that literal as a WITH TIMEZONE type, it'd first
get converted to a seconds-since-epoch value (respecting the timezone
specification) and then converted to WITHOUT TIMEZONE using the server's
timezone. If the server's timezone is not +1000, you get something other
than "05:00" inserted, which isn't what the client asked for.

So the driver deliberately doesn't do that, and in fact jumps through
some hoops to make sure it doesn't happen.

However, "interpret as WITH TIMEZONE then cast" is exactly the behaviour
you want in this case. You can get that behaviour via some explicit
casting, something like this:

CAST((CAST ? AS TIMESTAMP WITH TIMEZONE) AS TIMESTAMP WITHOUT TIMEZONE)

(modulo syntax errors; not sure if the outermost CAST is needed, off the
top of my head)

-O