Captain Caveman
Captain Caveman

Reputation: 1536

AWS Athena error: Value cannot be cast to timestamp

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.

Upvotes: 0

Views: 638

Answers (2)

Robert Kossendey
Robert Kossendey

Reputation: 7028

This should work:

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

Upvotes: 1

Guru Stron
Guru Stron

Reputation: 143328

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:

_col0
2022-08-04 11:30:00.000 UTC

Upvotes: 1

Related Questions