[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=13087444#comment-13087444 ] 

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

>  without timezone (GMT+1) - in this case no timezone has been set
> 2. with timezone (GMT+5) - in this case timezone has been set by adding -Duser.timezone=GMT+5 to JAVA_OPTS (only for Teiid server)

Please verify or explicitly set the timezone to be same across all vms/source to verify that the calendar fields stay the same.  What client are you using with Teiid?  As it will also matter what ResultSet.getTimestamp/getDate/getTime method is used.

Just changing the server will mean that the client should adjust the calendar fields.  For example ignoring Teradata if the server is in GMT-5 and there's a timestamp value with calendar fields "2001-01-01 13:00:00", then a client in GMT-1 using getTimestamp(col) will see the value "2001-01-01 09:00:00.0".  If the client is in GMT-1 and uses getTimestamp(col, cal) where cal is in GMT, then they will see "2001-01-01 08:00:00.0" instead.



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