Re: Timestamp Conversion Woes Redux

On 20-Jul-05, at 9:09 PM, Oliver Jowett wrote:

> Tom Lane wrote:
>
>> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>>
>>
>>> That's correct. A Timestamp is essentially a wrapper around
>>> milliseconds-since-epoch.
>>>
>>
>> OK --- that is also our interpretation of TIMESTAMP WITH TIME ZONE
>> (at the moment anyway --- there's some agitation for putting an
>> explicit zone identifier in too, because it looks like the SQL spec
>> requires that).
Reading the JDBC Tutorial suggests that this information would be
used when
retrieving the timestamp.
>>
>
> Ah ok, this is where some of my confusion is coming from -- I assumed
> that WITH TIME ZONE actually stored the timezone you provided and
> would
> preserve it on output. Guess I should test these things before
> wading in!
>
>
>>> The thing is that there are two distinct variants of setTimestamp():
>>>
>>
>>
>>
>>> (1) setTimestamp(index, timestamp)
>>> (2) setTimestamp(index, timestamp, calendar)
>>>
>>
>>
>>> (2) obviously maps to timestamp-with-zone.
>>>
>>
>> Hm, that's not obvious to me at all. If the timestamp is supposed
>> to be
>> unconditional absolute time, then the only sensible interpretation
>> of (1)
>> is that you're setting a timestamptz, and (2) would presumably
>> produce
>> a timestamp-without-tz value corresponding to the local time
>> readout of
>> the Calendar.
>>
>
> I was coming at it from the representations of the values:
>
> '2005-01-01 15:00:00' is a timestamp representation
> '2005-01-01 15:00:00+1300' is a timestamptz representation
>
> We don't have a timezone offset to send in case (1) so it "must" be a
> timestamp. But I can see where your interpretation comes from
> though --
> if the Timestamp is defined to be milliseconds-since-epoch we can
> identify the instant without needing a separate timezone offset.
>
> Reviewing it all I'm leaning towards JDBC's idea of TIMESTAMP being
> timestamp-without-timezone; the driver is meant to convert the
> absolute-instant of the Timestamp to a particular date/time using a
> specified (or default) timezone. That interpretation would make
> some of
> the setTimestamp() javadoc more comprehensible.
Well, there is documentation that suggests both are supported;
however apparently
not simultaneously.
>
> This would also explain why Oracle apparently has a separate extension
> setTimestamptz() method.
>
> Think I'm going to give up on this now though..
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
>