[teiid-dev] Connectors, data source, and timestamps

[Larry O'Leary]

On Tue, 2010-01-12 at 14:11 -0500, Steven Hawkins wrote:
> Java's notion of a timestamp is roughly the same as a dbms TIMESTAMP
> WITH TIME ZONE in that they both represent an absolute time.  So for
> Teiid once we've read in a timestamp, regardless of its source, it's
> supposed to represent an absolute time (source timezone information is
> lost).  For display and conversion purposes the the timezone of our
> timestamps is then taken to be the same as the default timezone of the
> Java instance we're running under.  
> 
> Connectors typically then do not have to concern themselves explicitly
> with time zones as long as the source contract is to create a
> timestamp object that represents the appropriate absolute time.

But from what I can tell, Oracle does not return a TIMESTAMP WITH TIME
ZONE as an absolute time.  If for example I set the time portion to
13:00 UTC, this timestamp comes back as 13:00 CST when my JDBC driver
(client) is in CST.  This is not the expected or same value as what was
inserted.  I would expect the value returned would either be 07:00 CST
or 13:00 UTC.

> ----- Original Message -----
> From: "Larry O'Leary" <loleary at redhat.com>
> To: "teiid-dev" <teiid-dev at lists.jboss.org>
> Sent: Tuesday, January 12, 2010 12:59:43 PM GMT -06:00 US/Canada Central
> Subject: [teiid-dev] Connectors, data source, and timestamps
> 
> I have been looking into some legacy issues regarding date/time values
> and time zone offsets.  I am well aware that the DBMS recommendation is
> to store time values in a consistent, predetermined time zone.  I think
> in principle this recommendation works but what about a federated query
> engine that has clients connecting from many different geographies?  
> 
> Also, how about the Oracle TIMESTAMP WITH TIME ZONE data type?  It seems
> that this even complicates matters more?
> 
> Are the connectors handling such logic?  Do they need to?  Does Teiid
> plan to offer a recommendation regarding setting and retrieving of time
> values and appropriate time zone or locale information?

--

Larry O'Leary
Middleware Support Engineering Group
Global Support Services
Red Hat, Inc.
loleary at redhat.com
1.866.LINUX70 (+1.314.336.2990) xt 81-62909
Office: +1.314.336.2909
Mobile: +1.618.420.7623
--
Delivering value year after year.
Red Hat ranks #1 in value among software vendors.
http://www.redhat.com/promo/vendor/