Re: Timestamp without Timezone and differing client / server tzs

Ken Johanson wrote:

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

Hi,

Not sure but this may be of some help. Or you might have already tried this....when you say
"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......."

to_timestamp(text, text)
to_char(timestamp, text)

>> insert into T values( to_timestamp('<<UTC datetime string>>', 'format'))

>> select to_char(timestamp, 'format') from T

client queries always deal in terms UTC datetime 'strings'. The conversion between string to timestamp and vice-versa happens on the server side independent of client's driver/ timezone.

And, these datetime strings can easily be generated and parsed back as you mentioned (SimpleDateFormat)

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.