Search code examples
sqlimpalaqsqlquery

WHERE condition on new created Column in Impala

In my Table, I have time information in UNIX time that I have converted to the proper time format using the following function in impala:
cast(ts DIV 1000 as TIMESTAMP) as NewTime.

Now I want to apply WHERE query on the newly created column "NewTime" to select the data from a particular time period but I am getting the following error:

"Could not revolve column/field reference: NewTime".

How can I apply WHERE query on the newly created column in impala. Thanks.

Solution

  • You can calculate it using inner subquery and then use it for filtering.

    select NewTime
from 
(select cast(ts DIV 1000 as TIMESTAMP) as NewTime,... from table) subq
where 
subq.NewTime >now()

    You can also use CTE like Gordon said.