Re: Timestamp Summary

Hi Oliver,

On 7/25/05, Oliver Jowett <oliver(at)opencloud(dot)com> wrote:
> Christian Cryder wrote:
> > I just wanted to take a few moments and summarize where we are in our
> > Timestamp woes issue. I'm also going to post a piece of code that
> > illustrates some of the problems we've experienced, and then try and
> > explain the conclusions we've arrived at. Hopefully this well help
> > others in the future.
>
> Why is another thread on this necessary?

Uh, just to try and concisely summarize what I had found? I wasn't
trying to start another discussion or anything...

> Did you try the patch I sent you off-list?

I don't think I received a patch from you off list.

I did talk to Dave this morning, who emailed me a new jar (which
contained your patch). And when I tried it, it works fine (except for
the fact that we still have to turn DST off on the client and the
configure the server timezone accordingly if we want to prevent data
munging. I'd rather I didn't have to do that, but I can live with it.
So I'm fine at this point...)

> > if (tz.useDaylightTime()) {
> > stmt.execute("set timezone='"+tz.getID().toLowerCase()+"'");
> > } else {
> > stmt.execute("set timezone='etc/gmt"+(roff<=0 ? "+" :
> > "")+(roff/-3600000)+"'");
> > }
>
> I have real problems with fiddling with the server timezone as discussed
> in the other thread, and I'd rather not see this patch applied. It makes
> a heck of a lot of assumptions about how Java/server timezones match up,
> and we have other options (namely using Unknown parameter types) that
> seem better.

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)

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.

c) even if you set DST off in the client, if its still turned on in
the server your date will still get munged on re-insert (even in your
code that I tested this morning).

d) we don't think we should have to turn DST off in both client and
server in order to guarantee that we can read values from the DB and
rewrite them untouched (as the same values).

So that's the long and the short of it - we're in MST, we have that
02:29 value in a DB column, we'd just like to be able to read it and
rewrite it without having to reconfigure either client or server to
some funky time zone. If you can suggest some other way of making that
happen I'd love to hear it.

Now, just to be clear - we can live with it this way. Currently w/ MS
SQL 2000 and NetDirect JDBC drivers, we have to do something similar -
configure the client jvm to non-DST in order to read/write w/out
munging. On Postrgres, we just have to add the one additional set of
setting the server timezone before reconnecting. So functionally, the
behavior is very close. And we're ok with this - our code is already
modified, etc.

But that's the problem that we'd still like to see a better solution for.

Please let me know if the problem still is not clear... (Dave, maybe
you can chime in if you see some ambiguity in my description, since I
think you have a handle on the issue we're wrestling with)

Thanks much for all your help guys...

Christian