Bug 1278724

Summary: Informix translator - date/time/timestamp values are adjusted differently according to timezone
Product: [JBoss] JBoss Data Virtualization 6 Reporter: Juraj Duráni <jdurani>
Component: TeiidAssignee: David Le Sage <dlesage>
Status: CLOSED CURRENTRELEASE QA Contact: Juraj Duráni <jdurani>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: 6.2.0CC: vhalbert
Target Milestone: ER1Keywords: Documentation
Target Release: 6.3.0   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Known Issue
Doc Text:
The Informix translator adjusts time values according to the default server's timezone or the DatabaseTimeZone translator property (the translator property takes precedence). However date and timestamp values are adjusted only according to the DatabaseTimeZone translator property. Timestamp values are adjusted only if the DatabaseTimeZone property is set. Moreover, time values are adjusted in the "opposite" direction to date/timestamp values.
 Story Points: ---
Clone Of: Environment:
Last Closed: 2016-08-24 11:43:19 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Juraj Duráni 2015-11-06 09:48:39 UTC 
The Informix translator adjusts only time value according to server's timezone. Timestamp values are adjusted only if 'DatabaseTimeZone' property is set. Date values are not adjusted at all.

I am not sure what is Teiid's logic in this case. Is the difference between server's timezone and database's timezone taken into account? Or only the timezone of the database?
-------------------

Server's timezone - GMT+1
DatabaseTimeZone property - not overridden
Result:
|time|date|timestamp|
|01:00:00|2000-01-01|2000-01-01 00:00:00.0|

Server's timezone - GMT+5
DatabaseTimeZone property - not overridden
Result:
|time|date|timestamp|
|05:00:00|2000-01-01|2000-01-01 00:00:00.0|

Server's timezone - GMT+5
DatabaseTimeZone property - GMT+1
Result:
|time|date|timestamp|
|01:00:00|2000-01-01|2000-01-01 04:00:00.0|

Server's timezone - GMT+5
DatabaseTimeZone property - GMT-1
Result (int this case
|time|date|timestamp|
|23:00:00|2000-01-01|2000-01-01 06:00:00.0|

Informix:
...
datevalue date,
timevalue datetime hour to fraction(1),
timestampvalue datetime year to fraction(1),
...

VDB - foreign table:
...
DateValue date OPTIONS (NATIVE_TYPE 'date', NAMEINSOURCE 'datevalue'),
TimeValue timestamp OPTIONS (NATIVE_TYPE 'datetime hour to fraction(1)', NAMEINSOURCE 'timevalue'),
TimestampValue timestamp OPTIONS (NATIVE_TYPE 'datetime year to fraction(1)', NAMEINSOURCE 'timestampvalue'),
...

VDB - view:
...
DateValue date,
TimeValue time,
TimestampValue timestamp,
...
AS ... DateValue, convert(TimeValue, time), TimestampValue, ...
Comment 1 JBoss JIRA Server 2015-11-09 18:37:19 UTC 
Steven Hawkins <shawkins> updated the status of jira TEIID-3808 to Resolved
Comment 3 David Le Sage 2016-08-09 00:39:09 UTC 
Release Note added.

URL:  https://access.qa.redhat.com/documentation/en/red-hat-jboss-data-virtualization/6.3/single/release-notes