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

Re: Timestamp without Timezone and differing client / server tzs

From: Pushker Chaubey <pchaubey(at)vertex(dot)co(dot)in>
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 07:23:05
Message-ID: (view raw, whole thread or download thread mbox)
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).
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.
While updating the timestamp values first we had to manipulate it to a 
value as it would be on a jvm with GMT timezone. And after reading it 
from any client on any timezone we were doing the reverse manupulation. 
Since we knew that read timestamp value is as per GMT time zone, we 
could manipulate it to as per clients' time zone.
And this protocol was followed by all the client applications.

But whenever I have control over schema I prefer storing LONG values 
directly (dates converted to millis) to using any database specific 
timestamp implementation. This keeps my code easily adaptable to various 
databases as I do not depend on Database behavior for dealing with 
timestamp objects.

Pushker Chaubey

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy the original message all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

Please do not print this email unless it is absolutely necessary.

In response to


pgsql-jdbc by date

Next:From: Heikki LinnakangasDate: 2008-07-09 08:38:20
Subject: Re: Patch to allow setting schema/search_path in the connectionURL
Previous:From: Oliver JowettDate: 2008-07-09 05:55:56
Subject: Re: Timestamp without Timezone and differing client / server tzs

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