I need to query for records with a timestamp no older than 7 days.

What type do I need to cast the timestamp field to remedy the error? Or, is there a more succinct / correct way to build the query?

Query:

SELECT report_timestamp
FROM my_table
WHERE to_unixtime(CAST(report_timestamp AS timestamp)) >= to_unixtime(CAST(now() - interval '7' day AS timestamp)) 

Error:

Value cannot be cast to timestamp: 1659612600

Thank you.

CodePudding user response：

Change CAST(report_timestamp AS timestamp) to from_unixtime:

select from_unixtime(1659612600);

Or to from_unixtime(cast(report_timestamp as integer)) (since you mentioned in the comments that report_timestamp is varchar):

select from_unixtime(cast('1659612600' as integer));

Output:

CodePudding user response：

This should work:

SELECT report_timestamp
FROM my_table
WHERE from_unixtime(CAST(report_timestamp AS bigint)) >= date_add('day', -7, now());