Re: Timestamp without Timezone and differing client / server tzs

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