Skip site navigation (1) Skip section navigation (2)

Re: Timestamp without Timezone and differing client / server tzs

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Ken Johanson <pg-user(at)kensystem(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp without Timezone and differing client / server tzs
Date: 2008-07-09 05:55:56
Message-ID: 487452EC.1010105@opencloud.com (view raw or flat)
Thread:
Lists: 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 that transmit 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

In response to

Responses

pgsql-jdbc by date

Next:From: Pushker ChaubeyDate: 2008-07-09 07:23:05
Subject: Re: Timestamp without Timezone and differing client / server tzs
Previous:From: Ken JohansonDate: 2008-07-09 05:25:52
Subject: Re: Timestamp without Timezone and differing client / server tzs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group