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

Re: Timestamp without Timezone and differing client / server tzs

From: Ken Johanson <pg-user(at)kensystem(dot)com>
To: Pushker Chaubey <pchaubey(at)vertex(dot)co(dot)in>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp without Timezone and differing client / server tzs
Date: 2008-07-11 04:49:09
Message-ID: 4876E645.4080503@kensystem.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Pushker Chaubey wrote:
> 
> 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).
> Just wondering, other apps using same database must also be facing the 
> same problem as you are if they operate across various timezones.
> Since all the applications are sharing the same timestamp(without TZ) 
> column they all should follow the same protocol to update and read 
> values for this shared timestamp(without TZ) column.
> 
> We had a similar situation where the schema (having a timestamp without 
> timezone column ) was not under our control and we had clients across 
> various timezone who accessed the database.
> If one client with timezone TZ1 updated the value, the other client with 
> timezone TZ2 did not read the correct value.
> To get around that we agreed on a reference timezone (GMT) so that 
> stored timestamp value would be as per GMT timezone.

I (and the other clients in their real timezones) are using a very 
similar config/protocol. The server is set to UTC and everyone agrees to 
convert the textual/iso8601 representation to it UTC value going out, 
and parse as UTC coming back. Just pass TZ to SimpleDateformat as one 
mean of accomplishing this. It's very easy conceptually. Well, only for 
query and their literal values constructed as in the StringBuffer way etc.

The kicker happens when using PreparedStatements or an overlying API 
that relies on them. We have no control (as I understand so far) over 
how the PG driver does conversion.

So I have to write a layer over some middleware that converts the values 
before passing down to PS (may not be possible though). The inelegant 
part is the server-specific config being stored not in the URL but 
elsewhere... I could get creative and piggyback my own param in the URL 
if I can access it.

So much to ponder. For now there's the political-correctness joy (not) 
of having to inform customer that right now anyway, PG cant do what 
they're doing with database and driver X.

Thanks for your thoughts Pushker,

Ken




In response to

Responses

pgsql-jdbc by date

Next:From: Ken JohansonDate: 2008-07-11 05:00:09
Subject: Re: Timestamp without Timezone and differing client / server tzs
Previous:From: MargaretGillonDate: 2008-07-10 17:43:26
Subject: Moving legacy application to JAVA, programming learning curve

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