Parse ISO date from S3 through Glue and Athena

Question & Answer
, ,
1

Hey guys !

Our setup is a small data lake in S3, a table defined in Glue, Athena querying the data, and Quicksight on top of it.

Everything works great but now i am trying to add a Date data onto the project.

In S3, we have data that are “json” containing date like this :
{ …, “date”:“2025-02-13T20:06:19.307Z”, … }
(i tried with both the Z at the end, and without. I know that Quicksight doesn’t support well the Z. I am OK to delete the Z at the end on the data that will be hosted on S3
In Glue table we defined a colum called “date” with type “DATE”. And in quicksight, i mapped this column, still called “date” but type “DATETIME”.

Now in quicksight, i use this date to do some filtering, and display too.
But my issue is, it seems the data is ALWAYS parsed (or else) as 00:00, the time is skipped completely, event tho the day is OK.

Screenshot 2025-02-13 at 21.59.00
Screenshot 2025-02-13 at 21.59.00674×272 5.94 KB

I tried different type of data in Glue, like string then parse, but i couldn’t make this work. I was hoping to have this handled out of the box, i want (if possible) to avoid calculated fields

Does someone has an idea to get me out of this ?

Thanks a lot !
Cheers

1 Like
2

Hello @hugob !

I would make sure that your date field is formatted in a way outlined here:

3

Hey @duncan !

Thanks for your quick answer and thanks for the link.
For those who have the same issue, what made it work for me :

In glue, the date column should be typed as TIMESTAMP
In S3, the date should be stored as yyyy-MMM-dd HH:mm:ss
In Quicksight it’s defined as DATETIME

Then everything would be fine

1 Like