Redshift query to delete 6 month old data

0
votes

I have a redshift table where scannable_id_latest_update_timestamp is the column containing epoch timestamp. I'm trying to run a query which returns all the rows where scannable_id_latest_update_timestamp is older than 6 months

Tried below query

select *
from scannable_id_pin_mappings
where date_cmp(dateadd(month,6,current_date), timestamp 'epoch' +
               scannable_id_latest_update_timestamp * interval '1 second') = -1;

Gettng exception

Invalid operation: function date_cmp(timestamp without time zone, timestamp without time zone) does not exist;

1
sqlamazon-redshift

1 Answers

0
votes

Assuming scannable_id_latest_update_timestamp is an epoch value you can compare them directly without a function by converting the other timestamp to an epoch.

SELECT EXTRACT( EPOCH FROM DATEADD( month , -6 , current_date ) ) ;
--  date_part
-- ------------
--  1542931200

SELECT *
FROM scannable_id_pin_mappings
WHERE scannable_id_latest_update_timestamp 
    < EXTRACT( EPOCH FROM DATEADD( month , -6 , current_date ) ) ;