CODEWITHSUNDEEP
hivehiveql
User12345
User12345

Reputation: 5480

get records for the last 7 days in Hive

I have a table in hive like below. I want to select insertdate, customer_id from this table where insertdate is current_date - 7 days.

original table

+------------------------+--------------+
|       insertdate       | customer_id  |
+------------------------+--------------+
| 2018-04-21 04:00:00.0  | 39550695     |
| 2018-04-22 04:00:00.0  | 38841612     |
| 2018-04-23 03:59:00.0  | 23100419     |
| 2018-04-24 03:58:00.0  | 39550688     |
| 2018-04-25 03:58:00.0  | 39550691     |
| 2018-05-12 03:57:00.0  | 39550685     |
| 2018-05-13 03:57:00.0  | 39550687     |
| 2018-05-14 03:57:00.0  | 39550677     |
| 2018-05-14 03:56:00.0  | 30254216     |
| 2018-05-14 03:56:00.0  | 39550668     |
+------------------------+--------------+

expected result

+------------------------+--------------+
|       insertdate       | customer_id  |
+------------------------+--------------+
| 2018-05-12 03:57:00.0  | 39550685     |
| 2018-05-13 03:57:00.0  | 39550687     |
| 2018-05-14 03:57:00.0  | 39550677     |
| 2018-05-14 03:56:00.0  | 30254216     |
| 2018-05-14 03:56:00.0  | 39550668     |
+------------------------+--------------+

But I am getting empty results when I try the following

select insert_date, customer_id from table where insert_date = date_sub(current_date, 7);

select insert_date, customer_id from table whereinsert_date = date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'), 7);

For both the above queries I got empty results.

What am I doing wrong here and how to get the correct result?

Upvotes: 2

Views: 8679

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Assuming you need data for the past 7 days, use

select insert_date, customer_id 
from table 
where to_date(insert_date) >= date_sub(current_date, 7) 
and to_date(insert_date) < current_date

The reason your query doesn't show results is because of the comparison between datetime format and date.

Upvotes: 5

Related Questions