How do I retrieve the timestamp from a mongodb object id in Athena

Question

I have the data moved from mongodb into AWS Athena. I need to extract the timestamp from the objectId of each of the record using SQL in Athena.

I could find some examples in Postgre, Redshift etc... but not in Athena...

https://stackoverflow.com/questions/22454804/how-to-retrieve-the-date-from-a-mongodb-objectid-using-sql

Example input:

507c7f79bcf86cd7994f6c0e

Wanted output in Athena:

2012-10-15T21:26:17Z

Answer 1

Translating this MySQL query looks pretty manageable using corresponding functions from the docs:

select from_base(substr('507c7f79bcf86cd7994f6c0e', 1, 8), 16);

Output:

            _col0
-----------------------------
 2012-10-15 21:26:17.000 UTC