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

Tuesday, 12 January 2010

Can you elaborate on your example?  How did you set the time to 13:00 UTC?  For my client
in CST:

CREATE TABLE tswtz_test (x TIMESTAMP WITH TIME ZONE);

insert into tswtz_test (x) VALUES (FROM_TZ(TIMESTAMP '2010-01-12 13:00:00',
'0:00'));

select x from tswtz_test;

returns:
2010-01-12 07:00:00.0

----- Original Message -----
From: "Larry O'Leary" <loleary(a)redhat.com&gt;
To: "Steven Hawkins" <shawkins(a)redhat.com&gt;
Cc: "teiid-dev" <teiid-dev(a)lists.jboss.org&gt;
Sent: Tuesday, January 12, 2010 2:03:38 PM GMT -06:00 US/Canada Central
Subject: Re: [teiid-dev] Connectors, data source, and timestamps

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(a)redhat.com&gt;
 To: "teiid-dev" <teiid-dev(a)lists.jboss.org&gt;
 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(a)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/

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009