Re: Issue with the JDBC driver doing timezone conversions on "java.sql.Date"?

Fred Janon wrote:

> When I check the dates in the database using PGAdmin III on the client,
> I see the dates in the client timezone (GTM +8).
> If I do a
> TimeZone.setDefault(TimeZone.getTimeZone("GMT"))
> in my application, I see the dates with the "correct" (or expected) GMT
> timezone.
>
> I don't understand why there is any timezone involved in reading and
> writing "timestamp without time zone".

Because "timestamp without time zone" is a little counterintuitive. It
represents a particular wallclock date/time; it does not represent a
unique instant in time.

It's roughly like storing the string "2009-12-21 21:00:00" - that time
interpreted in my timezone is right now, but it'll be some different
instant when interpreted in your timezone.

So when the JDBC driver needs to convert between a Java Timestamp -
which *does* represent a particular instant in time, in terms of
milliseconds-since-the-epoch - and a timestamp-without-timezone, it has
to pick a particular timezone to do that. The driver follows the JDBC
spec and uses the JVM's default timezone unless you explictly provide a
Calendar to use when calling getTimestamp()/setTimestamp().

If you're trying to store particular instants-in-time, you probably want
to use TIMESTAMP WITH TIME ZONE (which, again a little
counterintuitively, does NOT actually store a timezone - it just stores
milliseconds-since-the-epoch and converts that to/from different
timezones on demand)

-O