Subtract 7 days from today

Question

I am new to hive and sql.

Is there any way to get the current date - 7 in Hive, i.e. the date 7 days ago. And date in my table is in the format 20150910. (yyyyMMdd).

I tried below query, but it's not returning anything.

select *
from gmr.tedf_cs_mrch_tran
where cpd_dt = FROM_UNIXTIME(
                 UNIX_TIMESTAMP(
                  DATE_SUB(
                    FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 7 ), 'yyyyMMdd'))
limit 10;

Kindly please help me on this.

Answer 1

You can Use the Condition in where clause :

(((unix_timestamp(usage_dt,'yyyy-MM-dd')) >= (unix_timestamp(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') ,7),'yyyy-MM-dd'))) and ((unix_timestamp(usage_dt,'yyyy-MM-dd')) <= (unix_timestamp(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),'yyyy-MM-dd'))))

Here usage_dt is you column name for date, and the DateFormat In Hive used as "yyyy-MM-dd" format so if you are havig any other format you can mention as here yyyyMMdd is the customr format of the sate.

((unix_timestamp(usage_dt,'yyyy-MM-dd')) <= (unix_timestamp(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),'yyyyMMdd'))

There is also alternate option to use DateDiff in where condition like

(datediff(FROM_UNIXTIME(UNIX_TIMESTAMP()), usage_dt) <= 7)

Hope thses will work for you

Answer 2

Have you tried something like this?

select *
from table1
where dt >= date_sub(from_unixtime(floor(unix_timestamp()/(60*24*24))*60*24*24), 7) and
      dt < date_sub(from_unixtime(floor(unix_timestamp()/(60*24*24))*60*24*24)), 6);