Re: Timestamp Conversion Woes Redux

On 19-Jul-05, at 7:11 PM, Jeffrey Tenny wrote:

> Well, Oliver and Tom are very deep into things from their perspective,
> and I just wanted to offer an opinion from the legions of "app"
> developers. My opinion might not be what you expect, since I care
> less about compatability than some other things.
>
> What I care about most is that:
>
> 1) The conventions for standards-based constructs follow
> standards-based rules of engagement, so that if I ever
> get a clue about what the standard is, my code will be portable.

Well .... it will be based on the standard, but portable maybe, maybe
not.
There is considerable ambiguity in the standard, and other drivers
may implement
differently.
>
> 2) The code perform like screamin' demons. If setting it to
> UNKNOWN slows down all my parameterized query invocations,
> I'd rather not see that.
You wouldn't notice the affect of this.
>
> 3) If setString() used to work for timestamps and doesn't work
> now because of standards compliance changes,
> please just tutor me on the correct standards based call to make.
setTimestamp is the correct call to make
>
> 4) If the combined JDBC/backend stuff, when used correctly, is
> screwing up timezones, that's mission critical and needs to be
> fixed.

The challenge is this:

There is only one setTimestamp and there are two types of timestamps

1) timestamp with time zone
2) timestamp without time zone

Currently setTimestamp binds the parameter to timestamp with time
zone (timestamptz), the
problem arises when the underlying data type is a timestamp without
time zone (timestamp)
The backend automatically casts the timestamptz to a timestamp as per
the comments in the
backend code

/* timestamptz_timestamp()
* Convert timestamp at GMT to local timestamp
*/

Dave

>
>
> As far as application portability, I suspect most purveyors of
> heavy weight database apps view most any software upgrade, much
> less a complete vendor change, to be a "platform port" and so can
> fix these little incompatibilities as they debug the rest of their
> incompatibilities. (Whether it's adding "WITHOUT OIDS" to my pgsql
> CREATE TABLE statements for or "TYPE=INNODB" in futile attempts to
> get MySQL to give me transactionally safe tables).
>
> Heck, I'm still using 7.3. I skipped 7.4 because of transactions
> being stale on pooled connections, and I've been axiously looking
> forward to 8.0, but not if my timezones are shot to hell. In all
> this discussion I
> seem to have missed whether they're actually broken or whether I
> just need to make a "correct" call.
>
> Thanks for listening, and any further clarifications about my
> confusions are welcome.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>