- Newest
- Most votes
- Most comments
The eventTime property includes both date and time. Eg: '2023-07-14 00:00:41.000'. When including eventTime in your WHERE clause, make sure to consider the time part of it.
For example, let’s say now() is '2023-07-14 00:00:41.000'. If you do a filter criteria of eventTime = now(), you are actually doing eventTime = '2023-07-14 00:00:41.000'. In this case, you might missed some data in your result set because the query will only return events with eventTime = '2023-07-14 00:00:41.000'
Instead, you can use the WHERE clause in a sample scenario below:
- If you are trying to get events within today’s date use:
DATE_FORMAT(eventTime, '%Y%m%d') = DATE_FORMAT(NOW(), '%Y%m%d')
- If you are trying to get events in the past 7 days including the today’s date regardless of the time (hh:mm:ss.ms), use the criteria below
DATE_FORMAT(eventTime, '%Y%m%d') >= DATE_FORMAT(DATE_ADD('day', -7, NOW()), '%Y%m%d')
I don't think you can do that in CloudTrail Lake - you might need to look into using Athena. CloudTrail Lake has a big plus in being a managed service with less setup and learning curve than Athena, but it can be more expensive and as you've found it's pretty limiting. In Athena you can do e.g.: where timestamp >= date_format(now() - interval '7' day,'%Y/%m/%d') See this article for more info: "Querying AWS CloudTrail - Athena vs CloudTrail Lake" - https://www.linkedin.com/pulse/querying-aws-cloudtrail-athena-vs-lake-steve-kinsman/
A lot of new functionality has come onto the stage this week with the announcement of support for Presto SELECT statements.
select *
from INSERT-YOUR-EDS-HERE
where evenTime >= date_add('day',-7,current_timestamp)
ref: https://prestodb.io/docs/current/functions/datetime.html
This is now supported. Please check below launch announcement: https://aws.amazon.com/about-aws/whats-new/2023/05/aws-cloudtrail-lake-query-presto-sql-select-functions/
Example of how you can check: eventTime > (eventTime > date_add('day',-1, now()))
Relevant content
- asked 2 months ago
- asked a year ago
- asked 10 months ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 3 months ago
- AWS OFFICIALUpdated 3 months ago
- AWS OFFICIALUpdated 3 months ago