You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues-all@impala.apache.org by "Quanlong Huang (JIRA)" <ji...@apache.org> on 2019/03/01 01:31:00 UTC
[jira] [Comment Edited] (IMPALA-8241) from_utc_timestamp returns
inconsistent results with Hive
[ https://issues.apache.org/jira/browse/IMPALA-8241?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16780278#comment-16780278 ]
Quanlong Huang edited comment on IMPALA-8241 at 3/1/19 1:30 AM:
----------------------------------------------------------------
[~attilaj] [~tarmstrong], thanks for your clarification! I think this is rather a bug of Hive.
I struggled to understand the behavior of Hive's from_utc_timestamp UDF. It's quite a mess that it has different meanings for different parameter types due to the implicit cast in Hive. The most reasonable case is when the parameter is STRING. Comparing to Hive, Impala's semantic is quite friendly and understandable.
Our analysts just want to convert a column of unix timestamps in BIGINT to a column of time STRING in New York time zone, while the machine is in Los Angeles time zone. They can use
{code:java}
impala> select from_utc_timestamp(cast(0 as timestamp), 'America/New_York');
{code}
in Impala to get what they want. However, to achieve the same functionality in Hive, they should run in the following wired way...
{code:java}
hive> select to_utc_timestamp(from_utc_timestamp(0, 'America/New_York'), 'America/Los_Angeles');{code}
or
{code}
hive> select from_utc_timestamp(to_utc_timestamp(from_unixtime(0), 'America/Los_Angeles'), 'America/New_York');
{code}
was (Author: stiga-huang):
[~attilaj] [~tarmstrong], thanks for your clarification! I think this is rather a bug of Hive.
I struggled to understand the behavior of Hive's from_utc_timestamp UDF. It's quite a mess that it has different meanings for different parameter types due to the implicit cast in Hive. The most reasonable case is when the parameter is STRING. Comparing to Hive, Impala's semantic is quite friendly and understandable.
Our analysts just want to convert a column of unix timestamps in BIGINT to a column of time STRING in New York time zone, while the machine is in Los Angeles time zone. They can use
{code:java}
impala> select from_utc_timestamp(cast(0 as timestamp), 'America/New_York');
{code}
in Impala to get what they want. However, to achieve the same functionality in Hive, they should run in the following wired way...
{code:java}
hive> select to_utc_timestamp(from_utc_timestamp(0, 'America/New_York'), 'America/Los_Angeles');{code}
> from_utc_timestamp returns inconsistent results with Hive
> ---------------------------------------------------------
>
> Key: IMPALA-8241
> URL: https://issues.apache.org/jira/browse/IMPALA-8241
> Project: IMPALA
> Issue Type: Bug
> Reporter: Quanlong Huang
> Priority: Major
>
> This can be reproduced in both master and 2.x branches.
> {code}
> [localhost:21000] default> select from_utc_timestamp(cast(400000 * 3600.0 as timestamp), 'EST');
> Query: select from_utc_timestamp(cast(400000 * 3600.0 as timestamp), 'EST')
> Query submitted at: 2019-02-23 17:27:02 (Coordinator: http://impala-jenkins-slave-02:25000)
> Query progress can be monitored at: http://impala-jenkins-slave-02:25000/query_plan?query_id=f476c87a904f281:71588a2400000000
> +---------------------------------------------------------------+
> | from_utc_timestamp(cast(400000 * 3600.0 as timestamp), 'est') |
> +---------------------------------------------------------------+
> | 2015-08-19 11:00:00 |
> +---------------------------------------------------------------+
> Fetched 1 row(s) in 0.64s
> {code}
> {code}
> hive> select from_utc_timestamp(cast(400000 * 3600.0 as timestamp), 'EST');
> OK
> 2015-08-19 04:00:00
> {code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org