Re: Timestamp Conversion Woes Redux

Tom Lane wrote:
> 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.

I can understand backwards compatibility issues, but it doesn't make my
example above any more obvious :/

> 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.

I don't think this works, as we need to support the Calendar variants.
Specifically, given this code:

// 2005-01-01 05:00:00 UTC
Timestamp now = new Timestamp(1104555600*1000L);
Calender localCalendar = Calendar.getInstance(new Locale("en","NZ"));
ps.setTimestamp(1, now, localCalendar);

I would expect it to end up setting a timestamptz value of '2005-01-01
05:00:00 +0000' (or an equivalent instant as represented in some other
timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in
January). I think this is the correct interpretation given what the
javadoc says about setTimestamp() using the provided calendar to
construct an appropriate SQL TIMESTAMP value. The JDBC spec may be lousy
but I don't think they added the Calendar variants with the intention of
having drivers completely ignore that parameter..

I don't see how we can handle this even with TimeZone = UTC:

If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong
if implicitly cast to timestamp; if we pass it as timestamp then it's
just the wrong value immediately.

If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still
doesn't cast to the correct timestamp value; if we pass it as timestamp
then it is the right timestamp value, but is wrong if it is then cast to
timestamptz.

Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for
both timestamp and timestamptz as there's no implicit casting involved,
but we run the risk of getting the type wrong or not being able to
determine a type at all. Conversely, this is effectively what the V2
protocol path is doing anyway.

If we can't change the casting behaviour, and TimeZone hackery doesn't
work, and we want to support both timestamp and timestamptz via the
standard interfaces, then it seems like passing the parameters as
UNKNOWN is the only practical option :(

-O