CODEWITHSUNDEEP
hive
Bee
Bee

Reputation: 12502

Getting data of last hour

I need a help on creating a query. I have the following table.

Name(String) | Capacity(String) | Time(BIGINT)

I want to get rows which have Time in last hour. How can I do that?

I tried following but didn't work. 

select * 
from table1 
where Time > (unix_timestamp() - 60*60*1000);

Please help me.

Upvotes: 2

Views: 2431

Answers (2)

Bee
Bee

Reputation: 12502

Problem solved. It occurred because unix_timestamp() was given in seconds and I didn't notice that. Changing the code as follows worked.

select * from table1 where Time > (unix_timestamp()*1000 - 60*60*1000);

Thanks all.

Upvotes: 3

Balaswamy Vaddeman
Balaswamy Vaddeman

Reputation: 8530

I dont think there is a function for that .

One way is ,You have to take hour from unix in shell script

select * 
from table1 
where Time > ${hiveconf:onehourago};

write above code in a file

And call the above file like below in a shell script.

get hourfromunix by using date -d "1 hour ago" +%Y%m%d%H%M%S

Hive -hiveconf onehourago=$hourfromunix -f filename.hql

Upvotes: 1

Related Questions