Re: Timestamp Conversion Woes Redux

Holy schmoly, there's a lot of reading to catch up on here. Couple of
comments as I try to take it all in...

On 7/20/05, Oliver Jowett <oliver(at)opencloud(dot)com> wrote:
> Ok, so this sounds like JDBC doesn't distinguish with-timezone and
> without-timezone at all so trying to base the semantics on the spec is
> hopeless; we should just pick something that's sensible and do that.
>
> I still think my suggestion of using the two setTimestamp methods to
> support the two types makes sense -- but I'm out of time to deal with
> this, sorry.

I like Oliver's suggestion (because it would work for me) even though
I think Dave is probably right when he says

> > It would appear that the setTimestamp is intended to do one or the
> > other, but not both.

One issue which no one has really mentioned is that when we flatten a
timestamp to a string via toString() (eg. I ran into this even when
creating a custom PGTimestamp object yesterday) - when you flatten to
a String, you ARE using a calendar whether you realize it or not (eg.
the Default). Which means that you are performing a DST shift on the
client whether you like it or not. So if the date falls into the right
segment, it may still get munged.

The point here is that not only do we need to consider how to pass the
value to the server w/ TIMESTAMP rather than TIMESTAMPTZ, we also need
to consider how to render it to a string in PGobject.value w/out
munging the date. And the problem here (I think) is that you really
need to know the column info (w/ tz or w/out) in order to know whether
DST applies.

So that's something that needs to be considered. Right now, I can work
around the existing problems using PGTimestamp (per Dave's
suggestion), but I still have to tweak the timezone I am running in to
turn DST off in order to keep the dates from getting munged when
rendered to strings.

One other thing - could the problem be alleviated simply by

a) sending the data across the wire, always using UNKNOWN (thereby
deferring the decision to the DB)

b) sending the data across as millisecs value, rather than flattening
to a Timestamp string? That way to could avoid the toString() issues
mentioned above, plus it'd probably be faster to reconsitute from
millis on the server than by parsing a timestamp string anyways.

Those are just some thoughts to consider...
Christian