1

I have a query which runs good in Oracle, but i want to use the same query in Hive.

query: 

select count(mem_id)  
from mem 
where cobrand_id = '10001372' 
and user_type_id =1  
and status_ind <>3 
and LAST_ACCESSED >= to_epoch(sysdate-90);

Also, I have LAST_ACCESSED coulmn in double.example value of LAST_ACCEESSED is : 1.554386487E9, not sure what value this is i am guessing this could be seconds.

tried:

UNIX_TIMESTAMP( string date, string pattern )
FROM_UNIXTIME( bigint number_of_seconds  [, string format] )

No luck. Can someone help me. Thanks in advance.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
Somanath Patil
  • 35
  • 2
  • 10

1 Answers1

1

It seems 1.554386487E9 is the same unix epoch time stored in double, displayed in engineering notation, and it can be casted to BIGINT.

Checking your example:

select from_unixtime(cast(1.554386487E9 as bigint));
OK
2019-04-04 07:01:27

Does this timestamp look good?

If yes, then use unix_timestamp(concat(date_sub(current_date,90),' 00:00:00')) to get epoch time for current date - 90 days.

Your query fixed:

select count(mem_id)  
from mem 
where cobrand_id = '10001372' 
and user_type_id =1  
and status_ind <>3 
and cast(LAST_ACCESSED as BIGINT) >=  unix_timestamp(concat(date_sub(current_date,90),' 00:00:00'))
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks @leftjoin for your answer. i have tried the below. from_unixtime(unix_timestamp()-90*60*60*24, 'yyyyMMdd'); but i got the same results for 90 and 180 days and even without any days. I have replaced my query with your answer thanks again. – Somanath Patil Dec 06 '19 at 22:27