In hive we cannot calculate the difference between dates in minutes?
On Fri, Jul 20, 2012 at 6:06 PM, Techy Teck <[email protected]> wrote: > Whenever I am running 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(mi, > 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 am getting below exception as - Its happening in red color in above query > > *FAILED: Error in semantic analysis: line 10:157 Invalid Table Alias mi.* > * > * > But the same thing works fine in SQL Server. Anything wrong I am doing in > the red line? I am currently trying to see by that red line is if > difference between date is within 15 minutes. >
