amazon-athena - 如何在 Presto (Athena) 中将字符串转换为时间戳？

Question

我想将字符串的数据类型(例如:'2018-03-27T00:20:00.855556Z')转换为时间戳(例如:'2018-03-27 00:20:00')。
实际上我在 Athena 中执行查询:

select * from tb_name where elb_status_code like '5%%' AND 
date between DATE_ADD('hour',-2,NOW()) AND NOW(); 

但我有错误:

SYNTAX_ERROR: line 1:100: Cannot check if varchar is BETWEEN timestamp with time zone and timestamp with time zone

This query ran against the "vf_aws_metrices" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 6b4ae2e1-f890-4b73-85ea-12a650d69278.

原因 :
因为字符串格式的日期必须转换为时间戳。但我不知道如何转换它。

Answer 1

尝试使用 from_iso8601_timestamp .请访问以下地址了解更多时间戳相关功能:https://docs.starburstdata.com/latest/functions/datetime.html

presto:tiny> select from_iso8601_timestamp('2018-03-27T00:20:00.855556Z');
            _col0
-----------------------------
 2018-03-27 00:20:00.855 UTC
(1 row)

我相信你的查询应该是这样的:

select * from tb_name where elb_status_code like '5%%' AND 
from_iso8601_timestamp(date) between DATE_ADD('hour',-2,NOW()) AND NOW();