Re: Timestamp without Timezone and differing client / server tzs

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.

Regards,
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.