How to use casted column as where clause condition in hive?

Question

For example, the 'dt' is a string and I cast it to a date type, then I want to use it as a condition in WHERE clause, but it failed:

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where date<2021-11-08 and date >2021-11-01 limit 100;
FAILED: SemanticException Line 0:-1 Invalid table alias or column reference 'date': (possible column names are: mid, type, content, dt)

I already convert 'dt' to 'date', and then how to use it in the WHERE clause?

Answer 1

the casting you did in the select wouldnt be applicable it the where clause else you would have to set the casting in a subquery and apply the filtering in the outer query, or you could try this

hive> select mid,  cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) as date) from message_use_tags where cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) < 2021-11-08 and cast(to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))) >2021-11-01 limit 100;

Answer 2

Please try below code. Sometime auto conversion doesnt work for some tools. Better convert and trust the code and not the tool.

select mid,   to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd')))  dt_new --you dont have to cast to date. to_date will reove time part
from message_use_tags 
where 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) < to_date(from_unixtime(unix_timestamp('2021-11-08', 'yyyy-MM-dd')))  -- convert hardcode dates properly as well
and 
to_date(from_unixtime(unix_timestamp(dt, 'yyyyMMdd'))) > to_date(from_unixtime(unix_timestamp('2021-11-01', 'yyyy-MM-dd')))   -- convert hardcode dates properly as well
limit 100;

Pls note, i assumed column dt to be a string date in the format yyyyMMdd.