Re: Timestamp Conversion Woes Redux - Mailing list pgsql-jdbc

Hi Kris,

Dave and I have been talking about this a lot over the past couple of
days, and I've provided some sample code that will clearly illustrate
the problem. At the heart of the issue is the fact that there needs to
be a way to insert Timestamps into the DB using PreparedStatements,
where you can indicate to the DB "do not munge this date, thank you
very much". Right now, if you are using Timestamps, that will happen
regardless of whether you are declaring the timestamp with or without
timezone.

So to summarize: the sql standard has the notion of 2 kinds of
timestamp (w/, w/out timezone). The JDBC API on the other hand doesn't
- it's just "timestamp". And so the drivers have to make assumptions
about how to send the data across. The current implementation (which
uses TIMESTAMPTZ) _guarantees_ the server will remap the time. And
that is very much a problem (as my sample code illustrates, especially
when you are trying to store zoneless times).

As an example: consider what happens when you try and insert a time
like 2005-04-03 2:39:00. If daylight savings is turned on, this is not
a valid time, because it falls between 2 and 3 AM on the first Sunday
in April. So when you try and insert/update it into the db, it will
"adjust" it for you. But if this is zoneless data, then it's perfectly
legit. Unfortunately, there's no way via the JDBC PreparedStatement
(as currently implemented) to keep that munging from happening. And
that is very much a problem.

I should not have to configure my server to turn daylight savings off
in order to get stuff to insert into the DB correctly (after all,
other things run on the same server).

From my perspective, there needs to be a way for the JDBC driver to
know what type of timestamp you are working with. That information
resides in the DB, so you _could_ query the metadata info to get that
info up front. The problem w/ that of course is that it introduces a
performance penalty (unless you can come up w/ some kind of caching
strategy). So the only other option is to pass in some kind of hint to
the JDBC driver on the client side.

I'm not wild about referencing Postgres specific types in our code,
but if that's the only alternative, I can definitely live with it. I
DON'T like the idea of having to shove timestamps in via setString(),
because it seems hacky and counterintuitive (after all, they aren't
Strings!). It still seems to me that the easiest solution would be to
have setTimestamp() map with type UNKNOWN - the decision would get
delegated to the server, based on the column type definition, and the
only downside is that if someone tried to insert a timestamp into a
String column, it would work without erring (which it would do anyway
if we tweaked setString to use type UNKNOWN).

So those are my thoughts. Please feel free to holler if anything isn't clear...



>
> Kris Jurka
>