Re: Timestamp Conversion Woes Redux

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> i.e. currently the core of the problem is this behaviour:

> template1=# select '2005-01-01 15:00:00 +1000'::timestamptz::timestamp;
> timestamp
> ---------------------
> 2005-01-01 18:00:00
> (1 row)

Well, the current interpretation is that timestamptz -> timestamp
produces a timestamp representing what the timestamptz equates to in
your current TimeZone. I do not foresee changing that behavior when/if
we add explicit TZ info to timestamptz --- it would break just about
every existing app that uses this conversion.

In any case, this wouldn't solve Christian's complaint, because the root
of his problem is that the value ever goes through timestamptz at all.
That is necessarily going to "munge" values that don't correspond to
legal local times in whatever zone you are using.

The more I think about this, the more I think that the correct solution
must include having the driver set TimeZone = UTC (and complain if the
app tries to change it). Then you can specify parameter types as either
timestamp or timestamptz, it doesn't really matter, because conversions
between them on the server side will be no-ops. When you convert a Java
Timestamp to send to the server, you always convert it using a UTC
Calendar object. I'm not sure if the setTimestamp variants with a
Calendar are special in this regime; arguably you should ignore the
supplied Calendar, on the principle that you know what the Timestamp is
supposed to mean.

regards, tom lane