如何在AWS Athena中从MongoDB ObjectId提取时间戳

MongoDB ObjectId的前8个字符是记录生成时间的十六进制时间戳，我们可以通过Athena的SQL函数将其转换为标准UTC时间格式，具体实现如下：

核心逻辑 #

• 截取时间戳部分：用substr函数提取ObjectId的前8位十六进制字符，这部分对应Unix时间戳的十六进制表示。
• 进制转换：通过hex_to_int函数将十六进制字符串转为十进制的Unix时间戳（秒级）。
• 格式化为标准时间：使用from_unixtime函数将时间戳转为yyyy-MM-ddTHH:mm:ssZ格式的UTC时间字符串。

完整SQL示例 #

SELECT
  object_id,
  from_unixtime(hex_to_int(substr(object_id, 1, 8)), 'yyyy-MM-dd''T''HH:mm:ss''Z''') AS object_creation_time
FROM
  your_target_table; -- 替换为你的数据表名

测试验证 #

针对示例输入507c7f79bcf86cd7994f6c0e：

1. 截取前8位得到507c7f79
2. 转换为十进制时间戳1350353177
3. 最终输出2012-10-15T21:26:17Z，与期望结果一致。

内容的提问来源于stack exchange，提问作者Hemanth Gorijala