Re: Timestamp weirdness

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