[teiid-issues] [JBoss JIRA] (TEIID-3808) Informix translator - date/time/timestamp values are adjusted differently according to timezone

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3808?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13126394#comment-13126394 ] 

Steven Hawkins commented on TEIID-3808:
---------------------------------------

> 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?

We are simply leveraging the driver's relevant get/set methods.  There is no explicit check of the database server's timezone, rather the supply the Teiid server timezone or the database timezone via a Calendar. Typically if the default behavior is not as expected, then there may be a connection property or a set of session property that would make things more consistent.  In this case I can't find anything after a cursory search.

Beyond this if one would need dates to be timezone sensitive and informix is not, then more than likely the required workaround would be to not model them as dates, but as strings or to have a view layer that introduces normalizing modifytimezone functions.  So unless something else can be found, I'd say the resolution here is a KI that cautions about the inconsistencies of the informix driver/server.



> Informix translator - date/time/timestamp values are adjusted differently according to timezone
> -----------------------------------------------------------------------------------------------
>
>                 Key: TEIID-3808
>                 URL: https://issues.jboss.org/browse/TEIID-3808
>             Project: Teiid
>          Issue Type: Feature Request
>    Affects Versions: 8.7.1.6_2
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> 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:
> {code:sql}
> ...
> datevalue date,
> timevalue datetime hour to fraction(1),
> timestampvalue datetime year to fraction(1),
> ...
> {code}
> VDB - foreign table:
> {code:sql}
> ...
> 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'),
> ...
> {code}
> VDB - view:
> {code:sql}
> ...
> DateValue date,
> TimeValue time,
> TimestampValue timestamp,
> ...
> AS ... DateValue, convert(TimeValue, time), TimestampValue, ...
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)