CODEWITHSUNDEEP
sqldatabaseepochamazon-athenapresto
RJK
RJK

Reputation: 242

Amazon Athena - Query last 5 minutes of record (timestamp 19 digits)

I have a table in Athena where each record has a timestamp like 1531699933974236880.

The table has large amounts of data and I only want to query the last 5 minutes of records based on the timestamp.

What would the SQL statement be for this? I've tried using dateADD and date_diff but I'm not sure if I'm using it right, or if it's even supported in Presto (Athena's SQL query engine).

Any help would be great!

Upvotes: 0

Views: 3755

Answers (2)

Ashish
Ashish

Reputation: 5791

I would strongly recommend to have data partitioned based on timestamp so that you can use partition filter in your query to enhance the performance drastically.

If you have hourly partitioned data then maximum two partitions (n and n -1) would require to be processed. After this you can use the Gordon's example (along with partition filter) to write the query.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your timestamp looks like a Unix timestamp in microseconds. That would suggest:

select t.*
from t
where timestamp > (select max(timestamp) from t) - 5*60*1000000;

Your particular timestamp is interpreted as "Monday, July 16, 2018 12:12:13 AM", which seems reasonable.

Upvotes: 0

Related Questions