how to get results from Athena for the past week?

Question

I am using Amazon Athena to get all console login that happened in past week, right now i am able to just get all the console logins regardless of the data. I need to modify the below query so that this query picks up all the aws console logins that happened in last week always.

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,  
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin';

The eventTime looks like":

T

Answer 1

If that column is coming through as text, you could convert it into a timestamp. I found that Amazon Athena can convert '2016-05-03 05:46:00' into a timestamp, so use a replace() function to get it into the right format:

select cast(replace(replace('2016-05-03T05:46:00Z', 'Z'), 'T', ' ') as timestamp)

Therefore, in your WITH section, replace event.eventType with:

cast(replace(replace(event.eventType, 'Z'), 'T', ' ') as timestamp) AS eventType,

You can then use standard WHERE statements against the date, such as:

WHERE eventType > '2017-04-01'

Or for the past week (based on the Presto documentation):

WHERE eventType > current_date - interval '7' day

Answer 2

You can use from_iso8601_timestamp for the conversion like for example

SELECT *
FROM my_table
WHERE from_iso8601_timestamp(my_iso_field) > current_timestamp - interval '7' day