sqlhadoophivehiveqlscd2

Hive query to return single row based on eff and exp date

I have a table with the following data.

enter image description here

I am expecting row which needs to be returned is with exp_dt "2020-09-22". But when run below query it returning both the rows. I am not understanding why it is returning the first row also when it has eff_dt "2020-09-19".

select id,cd,eff_dt,exp_dt,post_dt from table 
where from_unixtime(unix_timestamp(eff_dt,"yyyy-MM-dd")) <= from_unixtime(unix_timestamp("2020-09-21","yyyy-MM-dd"))
and from_unixtime(unix_timestamp(exp_dt,"yyyy-MM-dd")) >= from_unixtime(unix_timestamp("2020-09-21","yyyy-MM-dd"));

Is there any issue with my query? I am expecting 2nd row to be returned.

Solution

  • Use < for the comparison to exp_date:

    select id,cd,eff_dt,exp_dt,post_dt
from table 
where from_unixtime(unix_timestamp('2020-09-21', 'yyyy-MM-dd')) >= from_unixtime(unix_timestamp(eff_dt, 'yyyy-MM-dd')) and
      from_unixtime(unix_timestamp('2020-09-22', 'yyyy-MM-dd')) < from_unixtime(unix_timestamp(exp_dt, 'yyyy-MM-dd'))

    I reversed the comparison order. I find it easier to follow the logic with the constants first.