CloudWatch Insights query: Format a DateTime string for grouping

Question

I have json-formatted CloudWatch logs whose entries resemble this:

{
    "message": "resource_liked",
    "context": {
        "date": {
            "date": "2021-05-07 16:52:11.000000",
            "timezone_type": 3,
            "timezone": "UTC"
        },
    ...

I am trying to write a CloudWatch insights query to make a simple histogram: number of events in the log per hour.

However, I can't use the @timestamp attribute of the log entry. I need to use context.date.date in the entry's message body instead.

Writing this query using @timestamp is simple enough:

stats count(*) by datefloor(@timestamp, 1h)

However, I'm not sure how I can use the message's context.date.date instead.

I assume I need to format the dateimes that look like 2021-05-07 16:52:11.000000 into something that aws understands as a datetime, but I can't find how.

---

What I've tried so far

stats count(*) by datefloor(context.date.date, 1h) -> "Invalid Date"

stats count(*) by datefloor(toMillis(context.date.date), 1h) -> "Invalid Date"

stats count(*) by datefloor(substr(context.date.date, 0, 19), 1h) -> "Invalid Date"

stats count(*) by datefloor(concat(replace(substr(context.date.date, 0, 23), ' ', 'T'), '-00:00'), 1h) -> Invalid Date. This one makes the field look exactly like how @timestamp is displayed.

Answer 1

| parse @message '"date": "*:' as hour
| stats count() as cnt by hour

Parsing a timestamp to use with CW Log Insights functions