Date Difference less than 15 minutes in Hive

Question

Below is my query, in which in the last line I am trying to see if the difference between the dates is within 15 minutes. But whenever I run the below query.

SELECT TT.BUYER_ID , COUNT(*) FROM
(SELECT testingtable1.buyer_id, testingtable1.item_id, testingtable1.created_time from (select user_id, prod_and_ts.product_id as product_id, prod_and_ts.timestamps as timestamps from testingtable2 LATERAL VIEW explode(purchased_item) exploded_table as prod_and_ts where to_date(from_unixtime(cast(prod_and_ts.timestamps as BIGINT))) = '2012-07-09') prod_and_ts RIGHT OUTER JOIN (SELECT buyer_id, item_id, rank(buyer_id), created_time, UNIX_TIMESTAMP(created_time)
FROM (
    SELECT buyer_id, item_id, created_time
    FROM testingtable1
    where to_date(from_unixtime(cast(UNIX_TIMESTAMP(created_time) as int))) = '2012-07-09'
    DISTRIBUTE BY buyer_id
    SORT BY buyer_id, created_time desc
) a
WHERE rank(buyer_id) < 5) testingtable1 ON (testingtable1.item_id = prod_and_ts.product_id AND testingtable1.BUYER_ID = prod_and_ts.USER_ID 
AND abs(datediff(testingtable1.created_time,FROM_UNIXTIME(cast(prod_and_ts.timestamps as BIGINT)))) <= 15) where prod_and_ts.product_id IS NULL ORDER BY testingtable1.buyer_id, testingtable1.created_time desc) TT GROUP BY TT.BUYER_ID;

I always get the exception as-

FAILED: Error in semantic analysis: line 10:144 Both Left and Right Aliases
Encountered in Join 15

Is there anything wrong with my query? Or in Hive we cannot calculate difference between dates in minutes? Any suggestions will be appreciated.

Answer 1

I think the problem is with your join. From the the Hive language manual:

Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.