Re: Timestamp without Timezone and differing client / server tzs

Hi all ,first let me describe the set up:

Server: Postgresql 8.3.x
Server's postgresql.conf:timezone = UTC
Server's OS (Linux tz): /etc/localtime -> /usr/share/zoneinfo/UTC

Client JDBC driver: PostgreSQL 8.3devel JDBC3g with SSL (build 602)
Client/JVM TZ : America/Denver

Now, execute query:

rs = st.exceuteQuery("SELECT NOW()");//understood to be a with-tz type
rs.next();
out.println(rs.getString(1)); --> 2008-07-08 07:09:59.284012+00
(expected, denver +0700)
out.println(rs.getTimestamp(1)); --> 2008-07-08 01:09:59.284012
(expected, implicit Denver time)

Now do a DML via prepared statement into a Timestamp without Timezone.

ps.setTimestamp(new Timestamp(System.currentTimeMillis()));

and select it:

out.println(rs.getString(1)); --> 2008-07-08 14:09:59.284012+00
(un-expected??)
out.println(rs.getTimestamp(1)); --> 2008-07-08 07:09:59.284012
(un-expected??)

Question: with ts without tz storage, should not a timestamp be
normalized to the servers implicit TZ (UTC in this case), since that is
set in the config's client-tz?

Also, is it possible/not to set the preferred TZ as a URL param to the
driver? This would be a safe option (not break apps), I believe. It
allows the string translation without tz to match the server's implicit
(zone-less string) tz.

Thanks,
Ken

Ken Johanson wrote:

> Now do a DML via prepared statement into a Timestamp without Timezone. [...]

This sort of thing is very sensitive to the exact schema and queries
used. Please send the actual schema & test code you're using to the list.

-O

Ken Johanson wrote:

> Question: with ts without tz storage, should not a timestamp be
> normalized to the servers implicit TZ (UTC in this case), since that is
> set in the config's client-tz?

If you don't specify a Calendar to setTimestamp, it's assumed you mean
the client JVM's default timezone (the same timezone that
Timestamp.toString() assumes). So if you have a Timestamp for 07:09 UTC,
and your client JVM's default timezone is +0700, then setTimestamp()
will send "... 14:09 +0700". That'll be stored as 14:09 in a
timestamp-without-timezone field. (Note that setTimestamp() doesn't know
if it's setting a with or without timezone parameter, so it always puts
a timezone in the value and lets the server ignore it where appropriate)

Similarly, if you select a ts-without-timezone value, it's turned into a
Timestamp representing that time in the client JVM's default timezone.

This behaviour is set up so that you get the intuitively correct results
such that e.g. Timestamp.getHours() matches the raw hours value of a
ts-without-timezone in the DB when you set or get it.

It doesn't really make any sense to use the server's timezone here,
because all the interpretation/formatting is happening on the client
side, and the standard Java libs know nothing about the server's timezone.

If you want to interpret a Timestamp as being in a particular timezone,
use the getTimestamp()/setTimestamp() variants that take an explicit
Calendar.

-O

Oliver Jowett wrote:
> Ken Johanson wrote:
>
>> Question: with ts without tz storage, should not a timestamp be
>> normalized to the servers implicit TZ (UTC in this case), since that
>> is set in the config's client-tz?
>
> If you don't specify a Calendar to setTimestamp, it's assumed you mean
> the client JVM's default timezone (the same timezone that
> Timestamp.toString() assumes).

.......

> If you want to interpret a Timestamp as being in a particular timezone,
> use the getTimestamp()/setTimestamp() variants that take an explicit
> Calendar.

So, short of passing a calendar to setTimezone, there is no other way to
indicate to the driver that zone-less date/time type values should be
translated to the server's different timezone, is this correct?

k

Ken Johanson wrote:

> So, short of passing a calendar to setTimezone, there is no other way to
> indicate to the driver that zone-less date/time type values should be
> translated to the server's different timezone, is this correct?

You could change the client JVM's default timezone I suppose.

I still don't understand exactly what you are trying to do, though.

-O

Oliver Jowett wrote:
> Ken Johanson wrote:
>
>> So, short of passing a calendar to setTimezone, there is no other way
>> to indicate to the driver that zone-less date/time type values should
>> be translated to the server's different timezone, is this correct?
>
> You could change the client JVM's default timezone I suppose.
>
> I still don't understand exactly what you are trying to do, though.
>

I could probably summarize it best by saying that I would like to place
all the server-specific info (including timezone) into the connection
URL, and leave the app to only pass what it and the jvm otherwise
considers to be a implicit/local timestamp.

To put it another way, Timestamp being a long integer, a point in time
that is the same anywhere, and we let the driver and database(s)
connectivity implicitly handle it's string nature in sql/iso8601.

This is useful where plural databases (not under my control) may exist
each in different timezones, and the app must deal with each
concurrently; where storing server configs (separate from the URL) and
passing Calendar objects for each, could be avoided.

k

Oliver Jowett wrote:
> Ken Johanson wrote:
>
>> So, short of passing a calendar to setTimezone, there is no other way
>> to indicate to the driver that zone-less date/time type values should
>> be translated to the server's different timezone, is this correct?
>
> You could change the client JVM's default timezone I suppose.
>
> I still don't understand exactly what you are trying to do, though.
>

To be sure, is the intent of the server's

timezone = UTC

config parameter, not to provide a hint to the clients about which
implicit timezone it treats zone-less strings as?

I have not dug in but am speculating that in the protocol some
indication of implicit tz may be provided.

Then again it may have no such intent at the client/connection level and
only be used for database date/time functions calculated against storage(?).

k

Ken Johanson wrote:
> Oliver Jowett wrote:
>> Ken Johanson wrote:
>>
>>> So, short of passing a calendar to setTimezone, there is no other way
>>> to indicate to the driver that zone-less date/time type values should
>>> be translated to the server's different timezone, is this correct?
>>
>> You could change the client JVM's default timezone I suppose.
>>
>> I still don't understand exactly what you are trying to do, though.
>>
>
> I could probably summarize it best by saying that I would like to place
> all the server-specific info (including timezone) into the connection
> URL, and leave the app to only pass what it and the jvm otherwise
> considers to be a implicit/local timestamp.
>
> To put it another way, Timestamp being a long integer, a point in time
> that is the same anywhere, and we let the driver and database(s)
> connectivity implicitly handle it's string nature in sql/iso8601.
>
> This is useful where plural databases (not under my control) may exist
> each in different timezones, and the app must deal with each
> concurrently; where storing server configs (separate from the URL) and
> passing Calendar objects for each, could be avoided.

But if you are using timestamp-without-timezone in each database, I
don't understand why the server timezone comes into the equation at all?
04:05 as a ts-without-tz in any database is going to end up as 04:05 in
the client JVM's default timezone regardless of what the server timezone is.

If you want to identify absolute points in time (as Timestamp does) then
you should be using timestamp with timezone, surely? The datatype name
is somewhat misleading: it doesn't actually store timezone info at all,
it actually identifies a unique point in time, and then represents that
in whatever timezone you request - i.e. it's a much better match to
java.sql.Timestamp than ts-without-timezone is.

Can you give me some concrete examples of schema + app code and how you
would want it to behave?

-O

Ken Johanson wrote:
> Oliver Jowett wrote:
>> Ken Johanson wrote:
>>
>>> So, short of passing a calendar to setTimezone, there is no other way
>>> to indicate to the driver that zone-less date/time type values should
>>> be translated to the server's different timezone, is this correct?
>>
>> You could change the client JVM's default timezone I suppose.
>>
>> I still don't understand exactly what you are trying to do, though.
>>
>
> To be sure, is the intent of the server's
>
> timezone = UTC
>
> config parameter, not to provide a hint to the clients about which
> implicit timezone it treats zone-less strings as?

That's not the intent as I understand it.

It tells the server how to interpret zone-less string literals (which
the JDBC driver never actually generates), how to cast between
with-timezone and without-timezone values by default, and which timezone
the default representation of with-timezone strings should use (which
doesn't change the instant in time they represent anyway). So the only
thing it should visibly affect if you're using JDBC is the casting
behaviour.

See section 8.5.1.3 in
http://www.postgresql.org/docs/8.3/static/datatype-datetime.html

-O

Oliver Jowett wrote:
>>> Ken Johanson wrote:
>> This is useful where plural databases (not under my control) may exist
>> each in different timezones, and the app must deal with each
>> concurrently; where storing server configs (separate from the URL) and
>> passing Calendar objects for each, could be avoided.
>
> But if you are using timestamp-without-timezone in each database, I
> don't understand why the server timezone comes into the equation at all?

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).

>
> Can you give me some concrete examples of schema + app code and how you
> would want it to behave?
>

Just to pass-in a timestamp, and which ever database it is sent to, be
stored with its real atomic (integer) value (ie string conversion
normalized to what db uses).

Simplified:

Timestamp NOW = new Timestamp(System.currentTimeMillis());
for (int i=0; i<SERVERS.length; i++)
{
Connection con = ....//SERVERS[i] etc; each server exists in different
timezone, and datetime/timestamps cols do not store zone.
PreparedStatement ps = con.prepareStatement("INSERT INTO tbl
(lastModified) VALUES (?)");
ps.setTimestamp(1, NOW));
ps.executeUpdate();
}

k

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)

This is the root of your problems, you're trying to use WITHOUT TIMEZONE
to represent an instant in time which only makes sense when you're using
the same timezone absolutely everywhere - and you're not. (Basically,
you've got an implicit extra bit of data floating around - the server
timezone - that's not correctly tied to your column data)

But if you can't change the schema, you can certainly work around it in
your application:

> Just to pass-in a timestamp, and which ever database it is sent to, be
> stored with its real atomic (integer) value (ie string conversion
> normalized to what db uses).
>
> Simplified:
>
> Timestamp NOW = new Timestamp(System.currentTimeMillis());
> for (int i=0; i<SERVERS.length; i++)
> {
> Connection con = ....//SERVERS[i] etc; each server exists in different
> timezone, and datetime/timestamps cols do not store zone.
> PreparedStatement ps = con.prepareStatement("INSERT INTO tbl
> (lastModified) VALUES (?)");
> ps.setTimestamp(1, NOW));
> ps.executeUpdate();
> }

Ok, so essentially you want to pass a timestamp-with-timezone value to
the server, then store it as timestamp-without-timezone, using the
server's timezone to do the cast, right?

What is biting you here is the server-side inference of the datatype of
your parameter. To support both WITH TIMEZONE and WITHOUT TIMEZONE via
setTimestamp(), the driver has to pass the parameter with an unknown
type and let the server infer the actual type.

For example if a client sets a timestamp of "05:00 +1000" (either via an
explicit Calendar, or because their default timezone is +1000) to insert
into a WITHOUT TIMEZONE column, the only sensible result is to insert
that as "05:00" regardless of the server's timezone setting. The client
sees the value as 05:00, so the only sensible thing the driver can do is
to insert it as 05:00.

The driver takes advantage of the fact that literals interpreted as
WITHOUT TIMEZONE completely ignore the timezone specification in this
case (and when inserting into a WITHOUT TIMEZONE column, the type of an
unknown-type parameter is inferred to be WITHOUT TIMEZONE).

If the driver passed that literal as a WITH TIMEZONE type, it'd first
get converted to a seconds-since-epoch value (respecting the timezone
specification) and then converted to WITHOUT TIMEZONE using the server's
timezone. If the server's timezone is not +1000, you get something other
than "05:00" inserted, which isn't what the client asked for.

So the driver deliberately doesn't do that, and in fact jumps through
some hoops to make sure it doesn't happen.

However, "interpret as WITH TIMEZONE then cast" is exactly the behaviour
you want in this case. You can get that behaviour via some explicit
casting, something like this:

CAST((CAST ? AS TIMESTAMP WITH TIMEZONE) AS TIMESTAMP WITHOUT TIMEZONE)

(modulo syntax errors; not sure if the outermost CAST is needed, off the
top of my head)

-O

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.

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

Oliver Jowett 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)
>
> This is the root of your problems, you're trying to use WITHOUT TIMEZONE
> to represent an instant in time which only makes sense when you're using
> the same timezone absolutely everywhere - and you're not. (Basically,
> you've got an implicit extra bit of data floating around - the server
> timezone - that's not correctly tied to your column data)

That's fine to me. UTC (in my case) is a well defined and agreed upon
TZ. IT works perfectly without having any conveyance of TZ since
everyone agrees to use UTC (or any other stable TZ spec).

>
> But if you can't change the schema, you can certainly work around it in
> your application:
>

I think that depends on how many if any layer of middleware are opaque
vs extensible. If I'm ostensibly only allow to pass in a number-wrapped
object java.util.Date or Timestamp then I could do as you suggest but by
offsetting the REAL / internal numeric value. Cringe. But I may have to
resort to this.

>> Just to pass-in a timestamp, and which ever database it is sent to, be
>> stored with its real atomic (integer) value (ie string conversion
>> normalized to what db uses).
>>
>> Simplified:
>>
>> Timestamp NOW = new Timestamp(System.currentTimeMillis());
>> for (int i=0; i<SERVERS.length; i++)
>> {
>> Connection con = ....//SERVERS[i] etc; each server exists in different
>> timezone, and datetime/timestamps cols do not store zone.
>> PreparedStatement ps = con.prepareStatement("INSERT INTO tbl
>> (lastModified) VALUES (?)");
>> ps.setTimestamp(1, NOW));
>> ps.executeUpdate();
>> }
>
> Ok, so essentially you want to pass a timestamp-with-timezone value to
> the server, then store it as timestamp-without-timezone, using the
> server's timezone to do the cast, right?
>
> What is biting you here is the server-side inference of the datatype of
> your parameter. To support both WITH TIMEZONE and WITHOUT TIMEZONE via
> setTimestamp(), the driver has to pass the parameter with an unknown
> type and let the server infer the actual type.
>

Well made point. But I'm fine with the driver implicitly treating ANY TS
object as convertible on a configured-as-such connection, even if its
destined for a TS w/ TZ. In this case because I know there are only
TZless storage.

> For example if a client sets a timestamp of "05:00 +1000" (either via an
> explicit Calendar, or because their default timezone is +1000) to insert
> into a WITHOUT TIMEZONE column, the only sensible result is to insert
> that as "05:00" regardless of the server's timezone setting. The client
> sees the value as 05:00, so the only sensible thing the driver can do is
> to insert it as 05:00.
>
> The driver takes advantage of the fact that literals interpreted as
> WITHOUT TIMEZONE completely ignore the timezone specification in this
> case (and when inserting into a WITHOUT TIMEZONE column, the type of an
> unknown-type parameter is inferred to be WITHOUT TIMEZONE).

Maybe that part/all the answer: if overriding TZ on connection then dont
pass redundant TZ information in protocol- just pass tz-less value
normalized/offset to the server/implicit TZ. I'm pretty sure another
driver does this too.

>
> If the driver passed that literal as a WITH TIMEZONE type, it'd first
> get converted to a seconds-since-epoch value (respecting the timezone
> specification) and then converted to WITHOUT TIMEZONE using the server's
> timezone. If the server's timezone is not +1000, you get something other
> than "05:00" inserted, which isn't what the client asked for.
>
> So the driver deliberately doesn't do that, and in fact jumps through
> some hoops to make sure it doesn't happen.
>
> However, "interpret as WITH TIMEZONE then cast" is exactly the behaviour
> you want in this case. You can get that behaviour via some explicit
> casting, something like this:
>
> CAST((CAST ? AS TIMESTAMP WITH TIMEZONE) AS TIMESTAMP WITHOUT TIMEZONE)
>
> (modulo syntax errors; not sure if the outermost CAST is needed, off the
> top of my head)
>
> -O
>

Honestly that beyond my comprehension, though part of me thinks it may
be over complicating things, if the driver instead just adds the offset
and doesn't pass zone in the string? In UTC's case anyway.

Thank you for some good ideas!

Ken

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.