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 <[email protected]> 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. > > >
