Re: Timestamp Conversion Woes Redux

Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.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).

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.

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