[teiid-issues] [JBoss JIRA] (TEIID-3562) Teradata15 - teiid shifts date/time/timestamp values according to timezone.
Juraj Duráni (JIRA)
issues at jboss.org
Mon Jul 13 05:03:05 EDT 2015
[ https://issues.jboss.org/browse/TEIID-3562?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Juraj Duráni updated TEIID-3562:
--------------------------------
Attachment: out_GMT_not_set
out_GMT+0500
Main.java
Sorry for late response.
My steps (vdb [1]):
- default timezone (GMT+01:00 in my case)
1. run server with default timezone (no -Duser.timezone property in JAVA_OPTS)
2. run Main.java (out_GMT_not_set)
- timezone GMT+05:00
1. add -Duser.timezone=GMT+05:00 to JAVA_OPTS in bin/standalone.conf
2. run server
3. run Main.java with additional system property -Duser.timezone=GMT+05:00 (out_GMT+0500)
I think that this could be a driver issue partially - "get*(int,Calendar) - default TimeZone" return time/date/timestamp which is already shifted, so teradata driver expects that value is stored in GMT and shifts it. As far as I can say, data in the Teradata are stored without information about timezone.
However, "Teradata - get*(int)" return original value, but "Teiid_teradata - get*(int)" return shifted value. Is this expected behavior?
[1]
{code:xml}
<vdb name="test" version="1">
<model name="tera">
<source name="teradata" translator-name="teradata" connection-jndi-name="java:/TeradataDS" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE smalla(IntKey integer,
DateValue date,
TimeValue time,
TimestampValue timestamp);
]]>
</metadata>
</model>
<model name="my">
<source name="mysql" translator-name="mysql5" connection-jndi-name="java:/MySQL55_PushDS" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE smalla(IntKey integer,
DateValue date,
TimeValue time,
TimestampValue timestamp);
]]>
</metadata>
</model>
<model name="pos">
<source name="postgres" translator-name="postgresql" connection-jndi-name="java:/postgres92" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE smalla(IntKey integer,
DateValue date,
TimeValue time,
TimestampValue timestamp);
]]>
</metadata>
</model>
<model name="ora">
<source name="oracle" translator-name="oracle" connection-jndi-name="java:/Oracle12_PushDS" />
<metadata type="DDL"><![CDATA[
CREATE FOREIGN TABLE smalla(IntKey integer,
DateValue date,
TimeValue time,
TimestampValue timestamp);
]]>
</metadata>
</model>
</vdb>
{code}
> 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)
More information about the teiid-issues
mailing list