hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bhagwan S. Soni" <bhgwnsson...@gmail.com>
Subject Re: comparing timestamp columns in Hive
Date Sun, 03 May 2015 23:18:46 GMT
I Tried it, and it seems it is working for me.

In your case t and tmp are two tables, but what are the tmp.object_id ?

can you provide t and tmp sample data!

On Mon, May 4, 2015 at 2:53 AM, Mich Talebzadeh <mich@peridale.co.uk> wrote:

> Hi,
>
>
>
> Just wanted to raise this one.
>
>
>
> Sounds like equating two columns of timestamp does not work in Hive. In
> the following both *t.op_time* and *tmp* *maxDDLTime* columns are defined
> as timestamp. When I do the following
>
>
>
> select t.object_id, t.op_type, t.op_time, tmp.maxDDLTime
>
> from t, tmp
>
> where t.object_id = tmp.object_id and t.op_time = tmp.maxDDLTime;
>
>
>
> it returns zero rows and does not work!
>
>
>
> However, when I cast timestamp columns to bigint it works
>
>
>
> select t.object_id, t.op_type, t.op_time, cast(t.op_time as bigint),
> tmp.maxDDLTime, cast(tmp.maxDDLTime as bigint)
>
> from t, tmp
>
> where t.object_id = tmp.object_id and cast(t.op_time as bigint) =
> cast(tmp.maxDDLTime as bigint);
>
>
>
>
> +--------------+------------+------------------------+-------------+------------------------+-------------+--+
>
> | t.object_id  | t.op_type  |       t.op_time        |     _c3     |
> tmp.maxddltime     |     _c5     |
>
>
> +--------------+------------+------------------------+-------------+------------------------+-------------+--+
>
> | 3644834      | 2          | 2015-05-01 12:42:51.0  | 1430480571  |
> 2015-05-01 12:42:51.0  | 1430480571  |
>
> | 3636987      | 2          | 2015-05-01 12:42:51.0  | 1430480571  |
> 2015-05-01 12:42:51.0  | 1430480571  |
>
>
> +--------------+------------+------------------------+-------------+------------------------+-------------+--+
>
>
>
> Is this expected? In other words to equate timestamp columns do we need to
> cast them to bigint or numeric?
>
>
>
> Thanks,
>
>
>
> Mich Talebzadeh
>
>
>
> http://talebzadehmich.wordpress.com
>
>
>
> Author of the books* "A Practitioner’s Guide to Upgrading to Sybase** ASE
> 15", **ISBN 978-0-9563693-0-7*.
>
> co-author *"Sybase Transact SQL Guidelines Best Practices", ISBN
> 978-0-9759693-0-4*
>
> *Publications due shortly:*
>
> *Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and
> Coherence Cache*
>
> *Oracle and Sybase, Concepts and Contrasts*, ISBN: 978-0-9563693-1-4, volume
> one out shortly
>
>
>
> NOTE: The information in this email is proprietary and confidential. This
> message is for the designated recipient only, if you are not the intended
> recipient, you should destroy it immediately. Any information in this
> message shall not be understood as given or endorsed by Peridale Ltd, its
> subsidiaries or their employees, unless expressly so stated. It is the
> responsibility of the recipient to ensure that this email is virus free,
> therefore neither Peridale Ltd, its subsidiaries nor their employees accept
> any responsibility.
>
>
>

Mime
View raw message