Re: Timestamp Summary

Christian Cryder wrote:

> If you can think of another way to do it, I'm all ears. Here's the
> crux of the issue (I feel like I've said this so many times now that
> I'm beginning to wonder if there is something fundamentally unclear in
> how I'm saying it).

> a) in a TIMESTAMP WITHOUT TIMEZONE column, 04-03-2005 02:29:00 is a
> perfectly valid time (you can manually insert this value from the
> console, or via a Statement)

Well I was expecting you to deal with it like this:

TimeZone utc = TimeZone.getTimeZone("UTC");
Calendar utcCal = Calendar.getInstance(utc);
// ...
Timestamp ts = rs.getTimestamp(1, utcCal);
// Deal with ts in terms of utcCal, e.g.:
//   utcCal.setTime(ts);
//   hours = utcCal.get(Calendar.HOUR);
// etc
stmt.setTimestamp(1, ts, utcCal);

In theory that should work just fine for any timestamp with no need to
meddle with either the JVM timezone or the server timezone.

The trick here is that any timestamp can be represented in UTC correctly
since UTC doesn't do any sort of daylight savings.

This isn't going to work in the current driver because it royally screws
up handling the with-Calendar variants of get/setTimestamp, but it
should be OK with my patched version.

Does that solve your problem? Am I missing something here?

> b) JDBC maps all TIMESTAMP columns to java.sql.Timestamp, which HAS
> the notion of Timezone in it. Meaning this date is going to get munged
> (because it's not a valid time in MST, for instance) as soon as you
> read it from the db, unless you set DST off.

This (Timestamp knowing about TimeZone) isn't generally true. The only
timezone-specific bit of Timestamp is the deprecated method that lets
you get the local JVM's default timezone offset. There's no timezone
info actually stored in the Timestamp. You generally want to be using a
Timestamp plus Calendar pair if you want to deal with times in a
particular timezone.

If you're trying to use a timezone that can't represent some date/time
values (such as, by the sounds of it, your default JVM timezone), then
I'm not suprised you're having problems. But that's really a design
issue in your app more than anything -- you're asking Timestamp to do
something that it *can't*.

-O