我在Athena表中有一列作为时间->字符串
我正在尝试转换为日期时间如下:
SELECT
"source"
, "account"
, CAST(time as date) Datetime
FROM
"testdata"
它给了我以下错误:
INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2021-11-28T08:04:21Z
您的时间戳看起来像是使用ISO 8601格式化的,而Athena有一个函数:from_iso8601_timestamp
SELECT
source,
account,
from_iso8601_timestamp("time") AS datetime
FROM testdata
如果你只想要日期部分,你可以将函数的结果转换为日期:
SELECT
source,
account,
CAST(from_iso8601_timestamp("time") AS DATE) AS "date"
FROM testdata
使用DATE_PARSE
:
SELECT source, account,
DATE_PARSE(time, '%Y-%m-%dT%H:%i:%sZ') AS Datetime
FROM testdata;