Re: Timestamp weirdness - Mailing list pgsql-jdbc

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@sss.pgh.pa.us> 07/24/05 5:48 PM >>>
Oliver Jowett <oliver@opencloud.com> writes:
> emergency.shower@gmail.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.