Re: Timestamp Conversion Woes Redux

Christian Cryder <c.s.cryder@gmail.com> writes:
>> The problem isn't with PreparedStatement, rather with Timestamp itself.

> Actually, I still think it is a problem w/ PreparedStatement, and I'll
> see if I can explain why, as well as provide a better test case to
> illustrate.

I'm hardly a JDBC expert, but I recall some considerable discussion
awhile back about how JDBC ought to map Java's (one) Timestamp type
into Postgres' TIMESTAMP WITH/WITHOUT TIME ZONE types, neither of
which apparently match the semantics of Timestamp very well.  You
should go digging in the pgsql-jdbc archives for background.

As for the problem at hand, I suspect that the driver is prespecifying
the parameter data type as either TIMESTAMP WITH or TIMESTAMP WITHOUT
TIME ZONE, and that whichever choice is used is different from what
the server would infer without the prespecification.  This would in
turn affect what the server assumes about the timezone spec (or lack
of one) in the supplied input string.  Worse, there could be an
ensuing run-time conversion between the two data types, leading to
adding or subtracting your local GMT offset.

(For that matter, is the parameter being sent in text or binary?
If it's binary then most of these theories fall to the ground...)

            regards, tom lane