Cloudwatch logs timestamp to date conversion

Question & Answer
,
1

I have Cloudwatch logs loaded into Athena tables but having trouble getting timestamp (strings) converted to dates.

Parsing the JSON from the ‘message’ column I can get the timestamp as a string - e.g. “2023-06-24T06:14:57Z”, however I cannot convert that string to a date for use in Quiksight using Presto SQL or calculations in Quicksight.

What is the secret ?

2

Hi @alQemay - Before doing it in QuickSight, can you try to write the select statement and change the format in Athena. Then you can directly using Custom SQL and paste the select statement and bring the data to QuickSight.

Regards - Sanjeeb

3

I have tried everyway I know how in Presto SQL (in Athena editor) but to no available. Same is true for QS calculations. I would like to see a real example that works - none of these do.

When using json_extract() to parse the “message” column the result is quoted, so that needs to be then Cast as VARCHAR to remove the quotes.

DATE(CAST(json_extract(message, ‘$.time’) as VARCHAR(45))) as tstamp,

Value cannot be cast to date: 2023-06-24T06:45:57Z

1 Like
4

Hi @alQemay - Can you give a sample json data to replicate it at my end?

Regards - Sanjeeb

5

Hi @alQemay

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.