[teiid-issues] [JBoss JIRA] (TEIID-3562) Teradata15 - teiid shifts date/time/timestamp values according to timezone.

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-3562:
---------------------------------------

By executing the source sql, do you mean through Teiid or through a different client?

Generally there are three timezones that come into play:

- The Teiid client (if different than the server, then the utc value for date types is shifted to keep the calendar fields consistent with the server)
- The Teiid server (if different than the source, it can be driver specific behavior as to how that is handled.  jdbc sources do support setting the database timezone property, which should make the retrieval of values specific to that timezone)
- The source

> Teradata15 - teiid shifts date/time/timestamp values according to timezone.
> ---------------------------------------------------------------------------
>
>                 Key: TEIID-3562
>                 URL: https://issues.jboss.org/browse/TEIID-3562
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 8.7.1.6_2
>         Environment: teradata version - 15.00.01.01
> teradata driver version - 15.10.00.05
>            Reporter: Juraj Duráni
>            Assignee: Steven Hawkins
>
> Teiid shifts date/time/timestamp values returned from teradata according to user.timezone value [1], [2]. However, when I execute source-specific command, teradata returns correct values [3].
> [1] 
> *Query:* SELECT * FROM smalla ORDER BY IntKey
> *-Duser.timezone:* GMT+5
> *Result:*
> || IntKey || DateValue || TimeValue || TimeStampValue ||
> |0 | 1999-12-31 | 19:00:00 | 1999-12-31 19:00:00.0|
> |1 | 2000-01-01 | 20:00:00 | 1999-12-31 19:00:01.0|
> |2 | 2000-01-02 | 21:00:00 | 1999-12-31 19:00:02.0|
> |3 | 2000-01-03 | 22:00:00 | 1999-12-31 19:00:03.0|
> |...|...|...|...|
> [2] 
> *Query:* SELECT * FROM smalla ORDER BY IntKey
> *-Duser.timezone:* GMT+1
> *Result:*
> || IntKey || DateValue || TimeValue || TimeStampValue ||
> |0 | 1999-12-31 | 23:00:00 | 1999-12-31 23:00:00.0|
> |1 | 2000-01-01 | 00:00:00 | 1999-12-31 23:00:01.0|
> |2 | 2000-01-02 | 01:00:00 | 1999-12-31 23:00:02.0|
> |3 | 2000-01-03 | 02:00:00 | 1999-12-31 23:00:03.0|
> |...|...|...|...|
> [3] 
> *Query:* SELECT g_0.IntKey AS c_0, g_0.DateValue AS c_1, g_0.TimeValue AS c_2, g_0.TimestampValue AS c_3 FROM smalla AS g_0 ORDER BY 1
> *local timezone:* GMT+1/GMT+5
> *Result:*
> || c_0 || c_1 || c_2 || c_3 ||
> |0 | 2000-01-01 | 00:00:00 | 2000-01-01 00:00:00.0|
> |1 | 2000-01-02 | 01:00:00 | 2000-01-01 00:00:01.0|
> |2 | 2000-01-03 | 02:00:00 | 2000-01-01 00:00:02.0|
> |3 | 2000-01-04 | 03:00:00 | 2000-01-01 00:00:03.0|
> |...|...|...|...|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)