CloudTrail Lake queries

Question

I'm looking into Cloudtrail Lake and need tips/help on regarding queries.
The given Query returns records as expected, however I need to queries where todays date is taken into consideratio,. This without having to rewrite the eventTimes dates every time.

Is there a function like **now()**, **current_date()** etc?

I see that supported date and time functions are using Presto 0.266 syntax, but are not able to find a solution to this issue
https://docs.aws.amazon.com/awscloudtrail/latest/userguide/query-limitations.html

```
SELECT
    awsRegion, recipientAccountId, count(*
    ) as numRec
FROM
   $EDS_ID
WHERE
    eventTime >= '2022-05-01 00:00:00'  #I want something like  now() - 7 days
    and eventSource = 'states.amazonaws.com' 
```

Answer

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:

1. If you are trying to get events within today’s date use:

DATE_FORMAT(eventTime, '%Y%m%d') = DATE_FORMAT(NOW(), '%Y%m%d')

2. 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')

Answer

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/

Answer

A lot of new functionality has come onto the stage this week with the announcement of support for Presto SELECT statements.

https://aws.amazon.com/about-aws/whats-new/2023/05/aws-cloudtrail-lake-query-presto-sql-select-functions/

```
select *
from INSERT-YOUR-EDS-HERE
where evenTime >= date_add('day',-7,current_timestamp)
```
ref: https://prestodb.io/docs/current/functions/datetime.html

Answer

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()))

CloudTrail Lake queries

Relevant content