Skip site navigation (1) Skip section navigation (2)

Re: Timestamp Conversion Woes Redux

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kris Jurka <books(at)ejurka(dot)com>,Christian Cryder <c(dot)s(dot)cryder(at)gmail(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Timestamp Conversion Woes Redux
Date: 2005-07-21 01:09:18
Message-ID: 42DEF5BE.1080109@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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).

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

In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2005-07-21 01:09:49
Subject: Re: Timestamp Conversion Woes Redux
Previous:From: Tom LaneDate: 2005-07-21 00:46:33
Subject: Re: Timestamp Conversion Woes Redux

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group