Re: Timestamp weirdness

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> emergency(dot)shower(at)gmail(dot)com wrote:
>> 2) When writing to a TIMESTAMP WITH TIME ZONE field, the driver should
>> not perform any time zone conversions and should store the Timestamp's
>> (UTC) y, M, d, H, m, s values directly to the database. The Calendar,
>> if given, should be ignored.
>>
>> 3) When writing to a TIMESTAMP WITHOUT TIME ZONE field, the driver
>> should calculate the Timestamp's y, M, d, H, m, s values in the given
>> Calendar's time zone and should store these values in the database.
>>
>> 4) When reading from a TIMESTAMP WITH TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> as UTC timestamp fields. The Calendar, if given, should be ignored.
>>
>> 5) When reading from a TIMESTAMP WITHOUT TIME ZONE field, the driver
>> should create a Timestamp by interpreting the y, M, d, H, m, s values
>> in the context of the given Calendar.

> (3 and 5: ... or the JVM's default Calendar if none is specified)

> This is also the behaviour I'd want, but I don't think I have 100%
> agreement on that from everyone yet.

Surely 4 should read "by interpreting the y...s values as a timestamp
in the zone specified as part of the value", not as necessarily UTC.
5 seems ok to me.

The difficulty with both 2 and 3 is that the driver has no very good way
of knowing whether it's writing to a timestamp with tz or one without.
We can know the parameter datatype we send, but if that gets converted
to the other type within the server, you're going to get burnt.

regards, tom lane