sql - Athena date_parse 用于具有可选毫秒字段的日期

Question

我在 S3 中有日期，使用它创建了 Athena 表。我在 S3 中有一些 json 格式的日期条目，在运行查询时，Athena 不接受这些条目作为日期或时间戳。

使用AWS Athena，它使用Prestodb作为查询引擎

示例 json:

    {"creationdate":"2018-09-12T15:49:07.269Z", "otherfield":"value1"}    {"creationdate":"2018-09-12T15:49:07Z", "otherfield":"value2"}

AWS Glue 将这两个字段都视为字符串，当我分别将它们更改为时间戳和日期时，围绕时间戳的查询不起作用，并在时间戳字段上给出 ValidationError 。

无论如何，我找到了一种使用 prestodb date_parse 函数的方法，但它也不起作用，因为有些字段有毫秒，而其他字段则没有。

    parse_datetime(creationdate, '%Y-%m-%dT%H:%i:%s.%fZ')    parse_datetime(creationdate, '%Y-%m-%dT%H:%i:%sZ')

两者都失败，因为存在不同的条目，即一个带有毫秒 %f，另一个没有有没有办法提供解析器、正则表达式，以便能够在 sql 查询执行期间将这些字符串转换为日期？

Answer 1

您可以使用 from_iso8601_timestamp 函数，而不是提供时间戳格式。

这样，所有时间戳都会被解析。

select from_iso8601_timestamp(creationdate) from table1;