Re: Timestamp Conversion Woes Redux

Tom Lane wrote:

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

That's correct. A Timestamp is essentially a wrapper around
milliseconds-since-epoch. It has no timezone information beyond some
badly thought out, not settable, "timezone offset" that always reflects
the default timezone, and some getHour/getMinute/etc accessors (again
using the JVM's default timezone only); these have been deprecated since
JDK 1.1 in favor of using Calendar objects.

Timestamps are then interpreted by feeding them through a Calendar for
locale- and timezone-specific formatting -- e.g. you can ask a Calendar
for the hour of a particular Timestamp, and it'll give you the
appropriate hour based on the timezone the Calendar is for.

> timestamp-without-zone is a fundamentally different critter, because it
> does not assume that there is any such thing as absolute GMT-equivalent
> time.

In other words, a timestamp-without-zone is just a
year-month-day-hour-minute-second tuple, which could correspond to many
instants in time depending on timezone and daylight savings changes?

The thing is that there are two distinct variants of setTimestamp():

(1) setTimestamp(index, timestamp)
(2) setTimestamp(index, timestamp, calendar)

(2) obviously maps to timestamp-with-zone. The question is what do we do
with (1) -- is the intention to set:

(a) a particular instant in time identified by 'timestamp', or
(b) the particular year-month-day-hour-minute-second described by
('timestamp' interpreted in the default timezone of the JVM)

1(a) is timestamp-with-zone again
1(b) is timestamp-without-zone

The JDBC spec doesn't help us here.

If we go with 1(a) then we have problems when casting to a
timestamp-without-zone value when the JVM and server timezones do not match.

If we go with 1(b) then we have problems when casting to a
timestamp-with-zone value when the JVM and server timezones do not
match, or when daylight savings means there are two possible instants in
a particular timezone identified by the timestamp-without-zone.

...

I'd like to go with 1(b):

I don't like 1(a) because it gives you no way of getting a
timestamp-without-zone into the database without having a timezone
available on the Java side that exactly matches the server timezone. As
found elsewhere in the thread, the two zic databases are already out of
sync, so I don't like the chances of that..

With 1(b), if you hit the casting problem, you have a workaround: use
the setTimestamp() method that takes a Calendar, and explicitly pass the
default JVM calendar. That completely avoids the need for the
problematic cast.

-O