Impala SQL将所有大于日期 - 日期列的所有记录分配为毫秒

Question

I'm looking to retrieve all rows in a table where last_modified_date is within the last 30 days. The date field is int and appears to be in milliseconds so not sure about the where condition. So a few steps here, convert the date field to standard date time then apply a date.或者，花时间并将所有记录取回30天。

SELECT  last_modified_date, 
        from_timestamp(CAST(CAST(last_modified_date as decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") as "Completed_Date" 
FROM    helix_access.chg_infrastructure_change limit 100;

我尝试了一个基本条件，但没有返回任何内容：

select from_timestamp(CAST(CAST(last_modified_date as decimal(30,0))/1000 AS timestamp), "yyyy-MM-dd HH:mm:ss.SSS") as "Completed_Date" 
from helix_access.chg_infrastructure_change limit 100;
where (Completed_Date) >= 2022-05-09 00:00:00.000

Answer 1

尝试此查询：

SELECT  last_modified_date, 
        FROM_UNIXTIME(last_modified_date), "yyyy-MM-dd HH:mm:ss.SSS") as "Completed_Date" 
FROM helix_access.chg_infrastructure_change 
WHERE FROM_UNIXTIME(last_modified_date), "yyyy-MM-dd HH:mm:ss.SSS") >= '2022-05-09 00:00:00.000'
limit 100;