Impala SQL to slect all records greater than a date - Date column is in milliseconds-CodePudding

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. Alternatively take the current time and get all records back 30 days.

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;

I tried a basic where condition but it's not returning anything:

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

CodePudding user response：

Try this query:

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;