例如,‘dt’是一个字符串,我将其转换为日期类型,然后我想将其用作WHERE子句中的条件,但失败了:
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)
我已经将‘DT’转换为‘Date’,然后如何在WHERE子句中使用它?
请尝试以下代码。有时自动转换对某些工具不起作用。最好转换并信任代码而不是工具。
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;
请注意,我假定dt
列是yyyyMMdd格式的字符串日期。