Re: Timestamp Conversion Woes Redux

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> I really don't think the driver should be touching TimeZone :/

The idea of tracking the server timezone and using it locally for
conversions of incoming data would avoid that gripe.

I am not sure however that Christian's basic complaint is solvable.
The problem here is very simple: the semantics of SQL TIMESTAMP WITHOUT
TIME ZONE do not match Java's Timestamp, no how, no way. For instance,
'2004-04-04 02:30' is an unconditionally valid timestamp-without-zone,
no matter what anybody's timezone setting is. However, if you try
to interpret it as local time in a US DST-observing zone, you have a
problem. You can never convert this value to timestamptz and back in
a DST-observing zone and not have it change ... which is basically
what Christian is hoping for. But there is *no* timestamptz value
that will decode as 02:30, because that's not a valid value for
timestamptz.

I think any solution that tries to work 100% for both flavors of SQL
timestamp is simply doomed to failure --- unless there are more
semantics to Java's Timestamp type than I've gathered from this
discussion. The impression I have is that Timestamp is supposed to
represent absolute time instants (ie, there's no additional "what time
zone is this in" info needed to determine the exact equivalent GMT
time), which would make it equivalent to timestamptz.
timestamp-without-zone is a fundamentally different critter, because it
does not assume that there is any such thing as absolute GMT-equivalent
time.

regards, tom lane