[JBoss JIRA] (TEIID-3562) Teradata15 - teiid shifts date/time/timestamp values according to timezone.
10:16 a.m.
[
https://issues.jboss.org/browse/TEIID-3562?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-3562:
---------------------------------------
I see two issues. The first is that our literal format logic is shifting the timezone
value in the wrong direction. This will be corrected by a separate JIRA as it still
won't resolve Teradata's behavior.
The other is that Teradata's handling is not expected. For example for me in GMT-5,
without any other settings, for intkey = 0 I see 2000-01-01 05:00:00.0 for the
timestampvalue. If I search using a literal comparison using 2000-01-01 00:00:00,
I'll still see 05:00:00 in the result. If I search using a preparedstatement using
2000-01-01 00:00:00, I get no results. - Both of those behaviors seem like driver issues.
Repeating the same with H2 or Oracle will provide the same 00:00 result with all
queries.
Setting the database timezone to GMT, then I'll see 2000-01-01 00:00:00.0 for intkey 0
from a select. A literal comparison with 2000-01-01 00:00:00 produces no results, but a
preparedstatement will work as expected. This again differs from other databases. A
possible fix is to modify the translator to not shift the literal value as it appears
teradata has some unexpected additional handling.
Alternatively I looked at having the teradata connection explicitly set the client time
zone without setting the database timezone using "set time zone
'GMT-5'". Here again the behavior seems inconsistent. While the desired
value 2000-01-01 00:00:00 is shown. Using that same value in a literal or prepared
statement binding returned no results.
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
Attachments: Main.java, out_GMT+0500, out_GMT_not_set
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)
3451
days inactive
3451
days old
0 comments
1 participants
participants (1)
-
Steven Hawkins (JIRA)