[
https://issues.apache.org/jira/browse/DRILL-5002?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15926115#comment-15926115
]
Vitalii Diravka commented on DRILL-5002:
----------------------------------------
The issue is related to the hive built-in functions with timestamp parameter as
well:
{code}
An example of the wrong behavior for the PDT timezone:
vitalii@vitalii-pc:~$ date
Wed Mar 15 05:55:21 PDT 2017
0: jdbc:drill:zk=local> SELECT TIMEOFDAY() FROM (VALUES(1));
+----------------------------------------------+
| EXPR$0 |
+----------------------------------------------+
| 2017-03-15 05:55:01.165 America/Los_Angeles |
+----------------------------------------------+
1 row selected (1.316 seconds)
0: jdbc:drill:zk=local> select from_utc_timestamp('1970-01-01 08:00:00','PST'),
to_utc_timestamp('1970-01-01 00:00:00','PST') from (VALUES(1));
+------------------------+------------------------+
| EXPR$0 | EXPR$1 |
+------------------------+------------------------+
| 1970-01-01 08:00:00.0 | 1970-01-01 16:00:00.0 |
+------------------------+------------------------+
The right output in hive for any timezone:
hive> select from_utc_timestamp('1970-01-01 08:00:00','PST'),
to_utc_timestamp('1970-01-01 00:00:00','PST') from hive_storage.testtable limit
1;
OK
1970-01-01 00:00:00 1970-01-01 08:00:00
Time taken: 1.981 seconds, Fetched: 1 row(s)
{code}
> Using hive's date functions on top of date column gives wrong results for
> local time-zone
> -----------------------------------------------------------------------------------------
>
> Key: DRILL-5002
> URL: https://issues.apache.org/jira/browse/DRILL-5002
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Hive, Storage - Parquet
> Reporter: Rahul Challapalli
> Assignee: Vitalii Diravka
> Priority: Critical
> Attachments: 0_0_0.parquet
>
>
> git.commit.id.abbrev=190d5d4
> Wrong Result 1 :
> {code}
> select l_shipdate, `month`(l_shipdate) from cp.`tpch/lineitem.parquet` where
> l_shipdate = date '1994-02-01' limit 2;
> +-------------+---------+
> | l_shipdate | EXPR$1 |
> +-------------+---------+
> | 1994-02-01 | 1 |
> | 1994-02-01 | 1 |
> +-------------+---------+
> {code}
> Wrong Result 2 :
> {code}
> select l_shipdate, `day`(l_shipdate) from cp.`tpch/lineitem.parquet` where
> l_shipdate = date '1998-06-02' limit 2;
> +-------------+---------+
> | l_shipdate | EXPR$1 |
> +-------------+---------+
> | 1998-06-02 | 1 |
> | 1998-06-02 | 1 |
> +-------------+---------+
> {code}
> Correct Result :
> {code}
> select l_shipdate, `month`(l_shipdate) from cp.`tpch/lineitem.parquet` where
> l_shipdate = date '1998-06-02' limit 2;
> +-------------+---------+
> | l_shipdate | EXPR$1 |
> +-------------+---------+
> | 1998-06-02 | 6 |
> | 1998-06-02 | 6 |
> +-------------+---------+
> {code}
> It looks like we are getting wrong results when the 'day' is '01'. I only
> tried month and day hive functions....but wouldn't be surprised if they have
> similar issues too.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
