Re: Timestamp weirdness

The Timestamp object in java doesn't have time zone info -- it is UTC.
The goal for timestamp with time zone columns, however it can be done,
is to have the UTC from a Timestamp object match the UTC of the column,
every time, in both directions. Time zone information should be
irrelevant for this column type.

In Java the role of a time zone with a TImestamp object is to render the
moment in time as a String representation of the local time for that
moment in a particular time zone, or to create a Timestamp moment from a
given local date and time.

I think Alex hit the nail on the head, with the elaboration that when a
Calendar object is not supplied (either the method without it is called
or a null is passed on invocation) the default time zone of the client
JVM should be used.

I don't have my head around the protocol used between the client and the
server, so I don't know if standard behavior can be acheived within that
protocol. To try to pin that down, could someone help me out and
clarify the following:

- I've seen mention of timestamp and timestampz, but I don't know the
scope of them. (Client side object types? Server side data structures?
Server data types? Protocol data element?) What are their
characteristics?

- I think I've seen mention that the value is turned into a
character representation of year, month, etc. for transfer over the wire
within the protocol. I don't know whether time zone info is allowed in
that context.

- I think I've also seen mention that the client side has no way of
knowing whether or not it is dealing with a column "with time zone".

If the protocol doesn't support passing time zone, and the client
doesn't know whether or not the data type it's sending is for a column
"with time zone", I have a hard time seeing how we can even come close
to handling both correctly.

If (hypothetically) timestampz is a protocol data element which does
include time zone, we might get to acceptable behavior if the JDBC
driver always converted the timestamp representation to the time zone
specified by the Calendar object and passed that time zone along. The
server would convert back to UTC for "with time zone" data; otherwise it
would ignore the time zone from timestampz and store the year, month,
etc. "as is". In the other direction, the server could pass "with time
zone" columns as timestampz using whatever time zone it wished (as long
as, with time zone info, it represented the right moment in time) -- the
JDBC driver would use the time zone to build the Timestamp object with
the right UTC offset. The server would have to pass "without time zone"
values as timestamp (no z), and the JDBC driver would take that as an
indication that it should use the given (or default) time zone to
interpret the value.

That last paragraph is all based a (hopeful) guess as to what goes over
the wire.

-Kevin


>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 07/24/05 5:48 PM >>>
Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> emergency(dot)shower(at)gmail(dot)com wrote:
>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> as UTC timestamp fields. The Calendar, if given, should be ignored.

Surely 4 should read "by interpreting the y...s values as a timestamp
in the zone specified as part of the value", not as necessarily UTC.
5 seems ok to me.

Kevin,

FYI, over the wire we have

The Oid of the type. Either timestamp, or timestamptz
then a string representation of the instance in time.

Now unless we look up the column type before sending it, we really
don't know which of the two it is.

We also have the option of using Oid unknown, which will invoke the
backends usual rules for unknown data.

Dave

On 25-Jul-05, at 11:47 AM, Kevin Grittner wrote:

> The Timestamp object in java doesn't have time zone info -- it is UTC.
> The goal for timestamp with time zone columns, however it can be done,
> is to have the UTC from a Timestamp object match the UTC of the
> column,
> every time, in both directions. Time zone information should be
> irrelevant for this column type.
>
> In Java the role of a time zone with a TImestamp object is to
> render the
> moment in time as a String representation of the local time for that
> moment in a particular time zone, or to create a Timestamp moment
> from a
> given local date and time.
>
> I think Alex hit the nail on the head, with the elaboration that
> when a
> Calendar object is not supplied (either the method without it is
> called
> or a null is passed on invocation) the default time zone of the client
> JVM should be used.
>
> I don't have my head around the protocol used between the client
> and the
> server, so I don't know if standard behavior can be acheived within
> that
> protocol. To try to pin that down, could someone help me out and
> clarify the following:
>
> - I've seen mention of timestamp and timestampz, but I don't
> know the
> scope of them. (Client side object types? Server side data
> structures?
> Server data types? Protocol data element?) What are their
> characteristics?
>
> - I think I've seen mention that the value is turned into a
> character representation of year, month, etc. for transfer over the
> wire
> within the protocol. I don't know whether time zone info is
> allowed in
> that context.
>
> - I think I've also seen mention that the client side has no
> way of
> knowing whether or not it is dealing with a column "with time zone".
>
> If the protocol doesn't support passing time zone, and the client
> doesn't know whether or not the data type it's sending is for a column
> "with time zone", I have a hard time seeing how we can even come close
> to handling both correctly.
>
> If (hypothetically) timestampz is a protocol data element which does
> include time zone, we might get to acceptable behavior if the JDBC
> driver always converted the timestamp representation to the time zone
> specified by the Calendar object and passed that time zone along. The
> server would convert back to UTC for "with time zone" data;
> otherwise it
> would ignore the time zone from timestampz and store the year, month,
> etc. "as is". In the other direction, the server could pass "with
> time
> zone" columns as timestampz using whatever time zone it wished (as
> long
> as, with time zone info, it represented the right moment in time)
> -- the
> JDBC driver would use the time zone to build the Timestamp object with
> the right UTC offset. The server would have to pass "without time
> zone"
> values as timestamp (no z), and the JDBC driver would take that as an
> indication that it should use the given (or default) time zone to
> interpret the value.
>
> That last paragraph is all based a (hopeful) guess as to what goes
> over
> the wire.
>
> -Kevin
>
>
>
>>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 07/24/05 5:48 PM >>>
>>>>
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>> emergency(dot)shower(at)gmail(dot)com wrote:
>>
>>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>>> should create a Timestamp by interpreting the y, M, d, H, m, s
>>> values
>>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>>
>
> Surely 4 should read "by interpreting the y...s values as a timestamp
> in the zone specified as part of the value", not as necessarily UTC.
> 5 seems ok to me.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>